Search code examples
postgresqlstored-proceduresplpgsqlpgtap

Postgresql return nested set of values in stored procedure


I'm currently doing a test in PostgreSQL using PGTAP.

In order to minimize redundancy in my code I placed duplicate code inside a function. I have functions that both returns a SETOF TEXT.

 CREATE FUNCTION _create_common_test(
        this_argument   varchar
    ) RETURNS SETOF TEXT AS $$
            DECLARE
               RETURN NEXT IS(this_argument, 'i_am_argument1', 'Checking Argument 1');
               MORE RETURN NEXT STATEMENTS HERE....
            END;
    $$ LANGUAGE plpgsql;


CREATE FUNCTION test_create_common_test_1() RETURNS SETOF TEXT AS $$
        BEGIN
            RETURN NEXT _create_common_test('i_am_argument1');
        END
    $$ LANGUAGE plpgsql;

CREATE FUNCTION test_create_common_test_2() RETURNS SETOF TEXT AS $$
        BEGIN
            RETURN NEXT _create_common_test('i_am_argument2');
        END
    $$ LANGUAGE plpgsql;

test_create_common_test_1 and test_create_common_test_2 calls the same function _create_common_test() inside their function varying only in the value passed in the argument.

As for my question, Is it possible to return the returned value of _create_common_test() which is a SETOF TEXT in the functions test_create_common_test_1 and test_create_common_test_2?

I have tried using PERFORM _create_common_test('i_am_argument2'),

CREATE FUNCTION test_create_common_test_2() RETURNS SETOF TEXT AS $$
        BEGIN
            PERFORM _create_common_test('i_am_argument2');
        END
    $$ LANGUAGE plpgsql;

but it does not enumerate the results I had inside the _create_common_test().


Solution

  • As correctly answered by @a_horse_with_no_name in the comments:

    return query select * from _create_common_test('i_am_argument2');