Executing string queries from function

Question

I have a table called rezultz with 1 row only containing valid SQL queries like: CREATE TRIGGER ... I'm trying to execute them with this function called get_all_rezultz() but it doesn't seem to work,any ideas why?

       CREATE OR REPLACE FUNCTION get_all_rezultz() RETURNS SETOF rezultz AS
    $BODY$
  DECLARE
r rezultz%rowtype;
   BEGIN
FOR r IN
    SELECT * FROM rezultz 
LOOP
    -- can do some processing here
    RETURN QUERY EXECUTE r; -- return current row of SELECT
END LOOP;
RETURN;
 END
  $BODY$
 LANGUAGE plpgsql;

  SELECT * FROM get_all_rezultz();

Here is the error I get :

  NOTICE:  identifier "CREATE TRIGGER userman_if_modified_trg AFTER    INSERT OR UPDATE OR DELETE ON userman FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); " will be truncated to "CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE O"
   CONTEXT:  SQL statement "("CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON userman FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ")"

PL/pgSQL function get_all_rezultz() line 10 at RETURN QUERY

    ERROR:  syntax error at or near ""CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON userman FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ""
    LINE 1: ("CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPD...
     ^
  QUERY:  ("CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON userman FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ")
      CONTEXT:  PL/pgSQL function get_all_rezultz() line 10 at RETURN QUERY

********** Error **********

   ERROR: syntax error at or near ""CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON userman          FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ""
    SQL state: 42601
     Context: PL/pgSQL function get_all_rezultz() line 10 at RETURN QUERY

Welcome to SO. What do you mean by "does not work"? Gives error? Does not deliver expected results? — Uwe Allner
– Uwe Allner, Commented Aug 31, 2016 at 8:22
This is not going to work. What is the actual problem you are trying to solve with this? A function can't return multiple results and you can't run a DDL statement using return query. You should take a step back and explain us the underlying problem you have. — user330315
– user330315, Commented Aug 31, 2016 at 8:34

score 1 · Accepted Answer · 2016-08-31 08:32:25Z

The variable r is a record containing multiple columns. It's not a scalar value (e.g. a string).

So you need to use the column name in the execute statement. Assuming the column in the table is called sql_statement you need to use:

RETURN QUERY EXECUTE r.sql_statement;

However, this will still not work because the select statement stored in that table will most definitely not return a results that is SETOF rezultz it returns a SETOF mytable if the query is select * from mytable.

You would need to specify RETURNS SETOF record but then you need to specify the column names and structure of the result when calling the function.

And even then this won't work because a function still only return a single result, not multiple results from multiple queries - which will happen if rezultzcontains more then one row.

If your SQL statements aren't actually SELECT statements as you have claimed, you will need to use EXECUTE you can't use RETURN QUERY for a CREATE TRIGGER statement.

I get the following error - ERROR: cannot open CREATE TRIGGER query as cursor
You can't run a CREATE TRIGGER statement with return query. But you claimed that the table "containing valid SQL queries like: SELECTFROM mytable*" no mentioning of create trigger there.

Collectives™ on Stack Overflow

Executing string queries from function

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related