I know this has been asked about multiple times in the past, sorry for the repeat I'm just trying to brainstorm a solution.

I'm looking for a way to query a table while excluding a small minority of the columns without having to query information_schema manually every time (since a lot of my tables consist of 50 or more columns only 2-3 of which are used for things that are irrelevant in the desired use case.)

I went about this like so:

query 1 returns the list of all the columns that exist on a table minus the ones you don't want.

        CREATE OR REPLACE FUNCTION public.inverse_columns(
        _table_name text DEFAULT NULL::text,
        _col_list text[] DEFAULT NULL::text[])
      RETURNS text[] AS
    col_arr text[];
    EXECUTE  'SELECT array_agg(column_name::text) FROM information_schema.columns
    WHERE table_name = ' ||quote_literal(_table_name) ||' AND 
    column_name NOT IN 
    ('||chr(39)|| array_to_string(_col_list, quote_literal(chr(44)))||chr(39)||');'
    INTO col_arr;

    RETURN col_arr;
      COST 100;
    ALTER FUNCTION public.inverse_columns(text, text[])
      OWNER TO postgres;

while query two uses the output array in another dynamic query in order to return the result set.

CREATE OR REPLACE FUNCTION public.except_select(
    _table_name text DEFAULT NULL::text,
    _col_list text[] DEFAULT NULL::text[])
ret_cols text[] := inverse_columns(_table_name, _col_list);
ret_vals record;
EXECUTE 'SELECT '|| quote_literal(array_to_string(ret_cols, chr(44)||chr(32))) ||
    'FROM ' || _table_name||';';
  COST 100;
ALTER FUNCTION public.except_select(text, text[])
  OWNER TO postgres;

the error I am receiving is this:

ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM except_select.....

and while I understand it is that I am misusing record, I can't think of a good way to return an undefined set of rows short of returning a refcursor and using that to populate a result set(which is what I am working on right now with slow progress unless I can get a better idea of how to do this.)

few days of playing around with it and I gave up.
  • So instead of listening the columns you want you want to list the columns you don't want. Why? Dec 17, 2016 at 9:24
  • Because I have a large volume of tables with 50 or more columns, and when setting up a webservice to pass data to a third party there are only 1 or 2 columns used for in-house record keeping that don't need to be included in the resulting dataset, I could query out the list of columns I want and copy paste it but even doing that frequently becomes tedious. Basically just to reduce the total number of keystrokes to increase my productivity. Dec 17, 2016 at 9:29

1 Answer 1


In SQL, it's not possible to have a function that simultaneously figures out what columns it should return and return them.

It's a consequence of the fundamental rule that the output structure of a SQL query must be known when parsing a query, before executing it.

If the client-side cooperates, that's not a problem. Just implement a two-step process:

step #1: call inverse_columns() that returns the SELECT statement as an string (or just the list of columns and your client code builds the rest of the statement).

step #2: run the SELECT statement.

There's no manual process involved if you can do client-side automation.

For instance newer versions of psql (9.6) have a \gexec function that executes a query and reinject the result as an SQL query.

Reworking a bit your except_select function to return a query instead of a function:

CREATE OR REPLACE FUNCTION public.except_select(_table_name text DEFAULT NULL::text, _col_list text[] DEFAULT NULL::text[])
AS $function$
ret_cols text[] := inverse_columns(_table_name, _col_list);
ret_vals record;
RETURN 'SELECT '|| array_to_string(ret_cols, chr(44)||chr(32)) ||
    ' FROM ' || _table_name;

Say we want to query all of pg_stat_activity except the query field, this could be written in psql as:

select except_select('pg_stat_activity', '{"query"}') \gexec

Result with expanded display (\x):

-[ RECORD 1 ]----+------------------------------
datid            | 12303
datname          | postgres
pid              | 15175
usesysid         | 10
usename          | daniel
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2016-12-17 16:10:07.200232+01
xact_start       | 2016-12-17 16:21:10.069285+01
query_start      | 2016-12-17 16:21:10.069285+01
state_change     | 2016-12-17 16:21:10.069288+01
wait_event_type  | 
wait_event       | 
state            | active
backend_xid      | 
backend_xmin     | 622
  • Unfortunately this is true for Postgres. In Oracle this would actually be possible. You can register call-back functions that tell Oracle how the structure of the select is going to look like. That allows you to do something like this: select * from table(some_function('select * from some_table')); and the result will contain the columns from some_table
    – user1822
    Dec 17, 2016 at 14:41
  • I was afraid of something like that, thanks for the detailed explanation. Dec 17, 2016 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.