Dynamic except query PostgreSQL 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
    $BODY$
    DECLARE
    col_arr text[];
    BEGIN
    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;
    END;
    $BODY$
      LANGUAGE plpgsql IMMUTABLE
      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[])
  RETURNS SETOF record AS
$BODY$
DECLARE 
ret_cols text[] := inverse_columns(_table_name, _col_list);
ret_vals record;
BEGIN
RETURN QUERY
EXECUTE 'SELECT '|| quote_literal(array_to_string(ret_cols, chr(44)||chr(32))) ||
    'FROM ' || _table_name||';';
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  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.)

    EDIT:
few days of playing around with it and I gave up.
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[])
 RETURNS text  LANGUAGE plpgsql IMMUTABLE
AS $function$
DECLARE
ret_cols text[] := inverse_columns(_table_name, _col_list);
ret_vals record;
BEGIN
RETURN 'SELECT '|| array_to_string(ret_cols, chr(44)||chr(32)) ||
    ' FROM ' || _table_name;
END;
$function$


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