2

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.
  • So instead of listening the columns you want you want to list the columns you don't want. Why? – JMD Coalesce Dec 17 '16 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. – SoDupuDupu Dec 17 '16 at 9:29
3

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
  • 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 – a_horse_with_no_name Dec 17 '16 at 14:41
  • I was afraid of something like that, thanks for the detailed explanation. – SoDupuDupu Dec 17 '16 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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