CODEWITHSUNDEEP
postgresqlplpgsqldynamic-sql
Aaron
Aaron

Reputation: 13

Executing string queries from function

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

Upvotes: 1

Views: 105

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions