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.