Geek asks and answers

Return selection from plpgsql function

I need to return a dynamically generated select statement from the plpgsql function. This is what I still have:

CREATE OR REPLACE FUNCTION qa_scf(cname character varying, tname character varying)
RETURNS text AS
$BODY$
BEGIN
return '* from ' ||tname|| 'where ' ||cname ||' != ''AL''';
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

The caller launched from the batch file:

select qa_scf('state', 'testtable')

This returns the literal text “qa_scf * from the test table, where state! =“ AL. ”I need to run this query from the sql batch file, but I cannot find the correct return statement so that this function returns a string and then executes its sql package I am using Postgres 9.0.

+3
source share
1 answer

The return type must be SETOF RECORD. Executing and returning an SQL file will become RETURN QUERY EXECUTE. Your query is missing SELECT. In addition, there were gaps.

CREATE OR REPLACE FUNCTION qa_scf(cname character varying, tname character varying)
RETURNS SETOF RECORD AS
$BODY$
BEGIN
    RETURN QUERY EXECUTE 'SELECT * from ' ||tname|| ' where ' ||cname ||' != ''AL''';
END;
$BODY$
LANGUAGE plpgsql;

, , . :

SELECT *
FROM qa_scf('foo', 'bar') AS t(col1_name col1_type, ...);
+9

More articles:


All Articles