Is there a way to create a dynamic number of columns in a plpgsql function return value?

Question

I have the following SQL code:

SELECT
    dip
    || ' '
    || (
        SELECT lib1 || ' ' || lib2
        FROM bds
        WHERE dip_s = dip
    ) AS label,
    COUNT(dip) AS freq
FROM current_table
WHERE v_deg = '3'
GROUP BY dip;

This code outputs a columns for the label, and another column for the number of occurence where v_deg='3'. The issue I have however is that I have multiple values of v_deg (a variable number of columns) and I need to add their respective column to the result.

I would have the following columns.

label|freq(v_deg='1')|freq(v_deg='2')|freq(v_deg='3')|freq(v_deg='4')|...

Since having a dynamic number of columns is not possible in basic SQL, I looked at plpgsql tutorials but I am clearly a beginner. Here's my try:

CREATE OR REPLACE FUNCTION GET_REM_T4(current_table text)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
DECLARE
    col_name text;
BEGIN
    FOR col_name IN 
        SELECT DISTINCT v_deg FROM current_table
    LOOP
        EXECUTE 'SELECT dip || '' '' || (
            SELECT lib1 || '' '' || lib2
            FROM bds
            WHERE dip_s = '||current_table.dip||'
            AND v_deg = '||col_name||
            ' )'
        INTO col_name;
        
        RETURN NEXT (col_name);
    END LOOP;
END;
$$;

The previous function outputs the following error when I put the correct parameter:

ERROR:  relation "current_table" does not exist
LINE 1: SELECT DISTINCT v_deg FROM current_table

I would like to know how to modify my function to get the desired ouput.

Answer 1

PL/pgSQL is just an extension of SQL. A function has to be called in an SQL statement, and the number, types, and names of returned columns must be known or declared at call time at the latest.

There are limited workarounds, but none works for your objective.

You could create the function with RETUNRS SETOF record. Or you could use polymorphism. But either only postpones the inevitable. Then you have to declare the return type with a column definition list or pass a row type in the call. But that only becomes evident after processing your query. Catch 22. I don't see how either could help you.

See:

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

Return an array column, or return multiple rows (one per distinct v_deg), or return a document type like json, jsonb, hstore or xml.

Or do two round trips to the DB server. One to determine the return type, and a second one with an according column definition list for your RETURNS SETOF record function, or with a matching row type for your polymorphic function. See:

• Return SETOF rows from PostgreSQL function

Also, what you are trying to do is rally a dynamic "crosstab" or "pivot" operation. See:

• Dynamically generate columns in PostgreSQL