How return dynamic number of columns in function?

Question

In PostgreSQL 11 database I have table with 6 column. Next function return static number of defined columns.

CREATE FUNCTION CALCULATION(INTEGER)
RETURNS TABLE(
    ORGANIZATION_ID INT4,
    ORGANIZATION_NAME VARCHAR,
    ORGANIZATION_RANG INT4,
    PARENT_ORGANIZATION_ID INT4,
    PARENT_ORGANIZATION_NAME VARCHAR,
    PARENT_ORGANIZATION_RANG INT4
) AS $$
    SELECT * FROM ANALYTICS;
$$ LANGUAGE SQL;

How can I make an SQL function in Postgres 11 which return a result set with dynamic number of columns according to a parameter passed in?

For example if I call SELECT * FROM CALCULATION(2);, function return first 2 columns.

If this is not possible with an SQL function, is it possible with a PL/pgSQL function?

Answer 1

This is possible for RECORD returning functions.

CREATE FUNCTION calculation(how_many integer) RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $fff$
BEGIN
    IF how_many = 1
        THEN RETURN QUERY SELECT 'foo'::text;
    ELSIF how_many = 2
        THEN RETURN QUERY SELECT 'foo'::text, 'bar'::text;
    END IF;
END;
$fff$
;

And now you can do:

jbet=> SELECT * FROM calculation(1) AS f(first_col text);
 first_col
-----------
 foo
(1 row)

jbet=> SELECT * FROM calculation(2) AS f(first_col text, second_col text);
 first_col | second_col
-----------+------------
 foo       | bar
(1 row)

The very serious downside is that each time you call the function you have to define set of returned columns, so I don't think you'll find this answer useful : )

Anyway, Postgresql needs to know returned type of each SELECT before it runs the query, so one or other way you have to define the columns.

JSON return value could be a reasonable answer if you just want the data and don't care if there are separate columns or not.

Answer 2

Backing up a step, why not use a standard select to get the columns you want from your set-returning function?

select organization_name,
       organization_rang,
       parent_organization_name,
       parent_organization_rang

  from calculation();

That's easy to follow and flexible. I'm guessing that you've written a simplified example and have a good reason for what you're asking...but I figured I'd double-check.