Call table functions from function in PL/pgSQL - is that possible?

Question

I have two similar functions with the same table as an outcome and I would like to create function to choose which of those will be execute.

My functions are create from the code like below - function_a and function_b:

CREATE OR REPLACE FUNCTION function_a(sys_id integer)
    RETURNS TABLE (
        col_1 text,
        col_2 text,
        col_3 text) AS $$
        (...)
        END; $$
    LANGUAGE 'plpgsql';

And I would like to get something like this (this code doesn't work but I think it expresses my intention):

CREATE OR REPLACE FUNCTION choose_function(system text, sys_id integer)
    RETURNS TABLE (
        col_1 text,
        col_2 text,
        col_3 text) AS $$
        BEGIN   
            IF system = 'A' THEN RETURN
                (select * from function_a(sys_id));
            ELSEIF system = 'B' THEN RETURN 
                (select * from function_b(sys_id));
            END IF;
        END; $$
    LANGUAGE 'plpgsql';

I receive an error: ERROR: RETURN cannot have a parameter in function returning set and hint: HINT: Use RETURN NEXT or RETURN QUERY. But use RETURN QUERY cause that I receive empty table or rather headers only and use RETURN NEXT give me also an error: ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters.

Is it possible to do what I need?

Pavel Stehule Pavel Stehule · Accepted Answer · 2019-09-14T03:57:36

It is possible without any problem. Every function has own output stack - output stack of top function is visible result. There is not one global output stack. So you cannot to call table function without storing their result in caller output stack. This can be done by command RETURN QUERY:

CREATE OR REPLACE FUNCTION f_a(a int)
RETURNS TABLE (b int, c int) AS
$$
  SELECT i, i+1 FROM generate_series(1,a)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION f_b(a int)
RETURNS TABLE (b int, c int) AS
$$
  SELECT i, i*10 FROM generate_series(1,a)
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION f2(par1 text, a int)
RETURNS TABLE (b int, c int) AS 
$$
BEGIN
  IF par1 = 'A' THEN
    RETURN QUERY SELECT * FROM f_a(a);
  ELSE
    RETURN QUERY SELECT * FROM f_b(a);
  END IF;
  RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE test(par1 text, a int);
INSERT INTO test('a', 10);
INSERT INTO test('b', 3);

SELECT f2.* FROM test, LATERAL f2(par1, a);

Call table functions from function in PL/pgSQL - is that possible?

1 Answers