5

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

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, ran from a batch file:

select qa_scf('state', 'testtable')

This returns the literal text "qa_scf * from testtable where state != 'AL'". I need to run this query from an sql batch file, but I cannot seem to find the right return statement to have this function return a string and then have the sql batch execute it. I'm using Postgres 9.0.

justanother1
  • 599
  • 3
  • 7
  • 17
  • The problem is likely to be in the caller of this function on how it processes the result, rather than in this function itself. – Daniel Vérité May 24 '12 at 09:04
  • I gave the caller above. That was my thought as well, however no one has given me an answer based around the caller. – justanother1 May 24 '12 at 13:05
  • What the question lacks is the caller's method to execute the SQL returned by the function. Or if you haven't done it yet and the question is "how to do it" (=dynamic SQL inside a script), Id' suggest [this answer](http://stackoverflow.com/questions/7942632/how-to-extrace-pg-backend-pid-from-postgresql-in-shell-script-and-pass-it-to-ano/8305578#8305578) – Daniel Vérité May 24 '12 at 14:53

1 Answers1

11

The return type should be SETOF RECORD. Executing and returning the SQL would become RETURN QUERY EXECUTE. Your query is missing SELECT. There was also whitespace missing before the where.

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;

Calling this function will get a little complicated as you will have to specify the columns you expect in the result. Goes like this:

SELECT *
FROM qa_scf('foo', 'bar') AS t(col1_name col1_type, ...);
Eelke
  • 20,897
  • 4
  • 50
  • 76
  • 5
    `returns table` should be preferred over `setof record` nowadays because it is much easer to use (no need to specify the table structure when selecting from it) –  May 23 '12 at 16:10
  • 5
    Yes but don't you need to know what the table structure is when you create the function to use returns table? – Eelke May 23 '12 at 16:20
  • This is going to be used in tables that have 25-30 columns a piece. It's a good answer, however it's highly impossible to give all of the column names each time. I'm trying to create this function in order to remove copying and pasting of certain queries, but it may be easier to just copy and paste it seems. – justanother1 May 23 '12 at 16:36
  • I'm also trying not to execute the query in the function as I've run into issues with that, I'm just trying to have it dynamically create the query string, return it, and run it in the SQL batch wrapper byt saying "Select qa_scf" – justanother1 May 23 '12 at 17:36
  • Did you find any useful solution to that problem (I have the exact same problem here: http://stackoverflow.com/questions/43519641/dynamically-add-a-column-with-multiple-values-to-any-table-using-a-pl-pgsql-func?noredirect=1#comment74093445_43519641) – ant1j Apr 20 '17 at 13:14