How Return Dynamic Number Of Columns In Function?

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

Solution 1:

This is possible for RECORD returning functions.

CREATEFUNCTION calculation(how_many integer) RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $fff$
BEGIN
    IF how_many =1THENRETURN QUERY SELECT'foo'::text;
    ELSIF how_many =2THENRETURN 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.

Solution 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

  fromcalculation();

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.