Create a function to return a dynamic table in Postgres PL/pgSQL

Question

The problem in my code is, i know that we cannot leave the return table without colums define in '()'

ERROR:  syntax error at or near "AS"
LINE 2: RETURNS TABLE AS $$

CREATE FUNCTION get_all_columns_from_dim_country()
RETURNS TABLE AS $$
DECLARE
    column_list text;
BEGIN
    SELECT string_agg(column_name || ' ' || data_type, ',') INTO column_list
    FROM information_schema.columns
    WHERE table_schema = 'my_schema'
        AND table_name = 'dim_country';
    
    RETURN QUERY EXECUTE
    'SELECT ' || column_list || ' FROM dim_country';
END;
$$ LANGUAGE plpgsql;

My purpose is create a function to return a dynamic output table, so i can transform, and many things to do with column in the begin and end block.

I have found a solution, but i dont know how to combine it into my situation, then i hope some propeople help me on this. Thank you very much for your help!

You could return a `SETOF record`, but at some point, you need to specify the columns that these records ought to have — Bergi, Jun 13 '23 at 03:46

score 0 · Answer 1 · answered Jun 13 '23 at 03:37

You cannot to returns dynamic table from function. Postgres (internally) is very strict typed system. The structure of queries should be known in planning time (optimization) - before query execution. So isn't possible to write function that returns some undefined tables.

There is only one possibility, but different - you can returns dynamic cursor from function, and later you can read data from this cursor by command FETCH, but you cannot to use SELECT command.

Create a function to return a dynamic table in Postgres PL/pgSQL

1 Answers1