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

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')

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related