PostgreSQL - How to dynamically execute a query in a function and return table

Question

I am trying to write a function, to which if I pass a variable then depending upon the value of the variable, different query should execute.

CREATE OR REPLACE FUNCTION SW_Versions(VersionType varchar)
RETURNS TABLE(array_sw_version varchar) AS $$
BEGIN

IF VersionType = 'All' THEN

    EXECUTE 'select ''1'' as array1_sw_version UNION ALL
        select ''2'' as array1_sw_version';

ELSIF VersionType = 'Major' THEN

    EXECUTE 'select ''A'' as array2_sw_version UNION ALL
        select ''B'' as array2_sw_version';

ELSE
    EXECUTE 'select ''X'' as array3_sw_version UNION ALL
        select ''Y'' as array3_sw_version';
END IF;

END;
$$ LANGUAGE plpgsql STRICT;

SELECT SW_Versions('Major')

The above query is not yielding the correct result which should be 2. Can anyone pls help me out in identifying what am I doing wrong?

How is it not working? Perhaps if you set up a SQL Fiddle, someone could help you figure out the problem. — Gordon Linoff
– Gordon Linoff, Commented Jun 9, 2015 at 18:14
Not sure why its not working..here is the output I am getting Imgur — user1552698
– user1552698, Commented Jun 9, 2015 at 18:20

klin · Accepted Answer · 2015-06-09 18:44:59Z

3

You do not need EXECUTE. Use RETURN QUERY instead. Casts (::varchar) added to provide column type compability.

CREATE OR REPLACE FUNCTION SW_Versions(VersionType varchar)
RETURNS TABLE(array_sw_version varchar) AS $$
BEGIN

IF VersionType = 'All' THEN

    RETURN QUERY select '1'::varchar as array1_sw_version UNION ALL
        select '2'::varchar as array1_sw_version;

ELSIF VersionType = 'Major' THEN

    RETURN QUERY select 'A'::varchar as array2_sw_version UNION ALL
        select 'B'::varchar as array2_sw_version;

ELSE
    RETURN QUERY select 'X'::varchar as array3_sw_version UNION ALL
        select 'Y'::varchar as array3_sw_version;
END IF;

END;
$$ LANGUAGE plpgsql STRICT;

SELECT SW_Versions('Major')

answered Jun 9, 2015 at 18:44

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - How to dynamically execute a query in a function and return table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related