Generic set returning function

Question

I have a plpgsql function which act as a wrapper around several other functions, all of which are set returning functions of different data types. Through this function, I am trying to get a generic set returning function which can return set of any data type. The data type of record is decided based on the input table name and column name.

Here is the code snippet:

create function cs_get(tablename text, colname text) returns setof record as $$ 
declare
    type_name text;
    ctype text;
    loadfunc text;
    result record;
begin                                              
    select data_type into type_name from information_schema.columns where table_name = tablename and column_name = colname;
     if type_name is null then 
         raise exception 'Table % doesnot exist or does not have attribute % ',tablename,    colname;
    end if;

    ctype := cs_get_ctype(type_name);
    loadfunc := 'select * from cs_get_'||ctype||'('''||tablename||''','''||colname||''')';
    for result in execute loadfunc loop
        return next result;
    end loop;
    return;                                                      
end; $$ language plpgsql;

Suppose the column is of type integer (corresponding c type is int64), loadfunc would be

select * from cs_get_int64(tablename, colname)

cs_get_int64 is a set-returning function defined in a C library which returns values of type int64.

However, this gives an error saying

ERROR:  a column definition list is required for functions returning "record" at character 15

The easiest way to achieve this is to return a setof text for every data type. But that of course is not a clean way to do this. I have tried replacing loadFunc with

select cs_get_int64::integer from cs_get_int64(tablename, colname)

which is required to use records. But, this did not help.

My question now is: Is it possible to create such a generic set returning function? If yes, how?

Answer 1

The answer is yes. But it's not trivial.

As long as you return anonymous records (returns setof record) a column definition list is required for the returned record to work with it in SQL. Pretty much what the error message says.

There is a (limited) way around this with polymorphic types:

CREATE OR REPLACE FUNCTION cs_get(_tbl_type anyelement, _colname text)
  RETURNS SETOF anyelement AS

...

Detailed explanation int this related answer (climax in the last chapter!):

• Refactor a PL/pgSQL function to return the output of various SELECT queries