Columns are not dynamic. They can't be suppressed on the result because at that point it's too late. You have two options
1 | foo |1|2
1 | foo | x |2
1 | foo | y |2
The schema is fixed from that point and you can return just what you need.
Use dynamic SQL. The server returns sql that you issue against the server.
You could also fix the schema, if most of those columns are redundant modify the schema.
I think perhaps you misunderstood my goal. I'm working with a query that returns something like: [...] I want to turn hide/supress the columns which are "constant," as in they don't change from row to row. That would turn the above table into...
I'm very sure I don't misunderstand your goal. We have at least one question a week that is along the same lines we have to turn around for the very same reason. In fact, a year ago yesterday I asked a similar question out of desperation thinking there may be a better way, and was turned around for the same reason.. I can still quote Erwin's relevant parts here,
Column names and types cannot be dynamic. SQL demands to know number, names and types of resulting columns at call time. Either by explicit declaration or from information in the system catalogs (That's what happens with
SELECT * FROM tbl: Postgres looks up the registered table definition.) ... You want Postgres to derive resulting columns from data in a user table. Not going to happen. [...] One way or the other, you need two round trips to the server. Either you create a cursor and then walk through it. Or you create a temp table and then select from it. Or you register a type and use it in the call.
Your use case here is slightly different, but you should still read the above bolded parts at least 15 times, because it has to sink through.
Then you've got to either,
- Change the format you're will to accept from the server.
- Process the result set on the server that generates the right query, and then issue that query back to the server.
For your example
CREATE TABLE tinyexample(id,ident,key,val)
( 1, 'foo', 'x', 2),
( 1, 'foo', 'y', 2);
What you want is something like this. Note this is not the best way to do this, you could save a round trip by running this in a function.
SELECT FORMAT (
'SELECT %%s FROM %%I.%%I.%%I;',
array_to_string(array_remove(ARRAY[%s]::text,null), ', '),
) AS inner
) AS outer
string_agg( format($$CASE WHEN count(DISTINCT %I) > 1 THEN %s END$$, column_name, quote_literal(column_name)), ', ' ) AS colexpr
WHERE table_catalog = 'test'
AND table_schema = 'public'
AND table_name = 'tinyexample'
GROUP BY table_catalog, table_schema, table_name
) AS t;
This seems like a mess, most of that's not me though. We have
- Issue our first query to the information_schema to get the column listing. Without this you'd have to statically type the columns. That's no fun. This returns query "outer".
- Issue our second query ("outer") to find out which columns are not distinct based on their values. This returns query "inner".
- Issue our third query ("inner") which returns the resultset we want.