CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user1552698
user1552698

Reputation: 597

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

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?

Upvotes: 1

Views: 154

Answers (1)

klin
klin

Reputation: 121834

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

Upvotes: 3

Related Questions