PostgreSQL – Passing ROWTYPE Parameter to EXECUTE

dynamic-sqlplpgsqlpostgresqlpostgresql-8.3

I am developing a function in Postgres which aims to recover for each record of a query the value of a result of a check contained in a set of functions. Only one of these functions will return the correct value. These functions have a common prefix 'fn_condition_' and receive an object of type 'my_table' as parameter.

As the number of functions that make the check is unknown, I decided to consult the Postgres catalog, from the table pg_catalog.pg_proc searching for functions with the prefix 'fn_condition_' and dynamically execute them with EXECUTE.

My problem is the correct form to pass the parameter for EXECUTE.

create or replace function test_conditions()
returns void as 
$$
declare
    v_record my_table%rowtype;
    v_function pg_proc%rowtype;    
begin 
    set search_path = 'pg_catalog';

    for v_record in (select * from my_table where id in (1,2,3)) loop
        for v_function in (
            SELECT  p.proname
            FROM    pg_namespace n
            JOIN    pg_proc p
            ON      p.pronamespace = n.oid
            WHERE   n.nspname = 'operacional'
            and p.proname like ('fn_condition\\_%')
            order by p.proname) 
        loop
         -- execute 'select ' || v_function.proname || '(' || v_record || ')';  -- ???
        end loop;
    end loop;
end;
$$ 
language plpgsql;

How to pass v_record properly in the commented EXECUTE command in the function above?

execute 'select ' || v_function.proname || '(' || v_record || ')';  -- ???

Example function:

create or replace function fn_condition_1(p_record my_table)
returns bigint as 
$$
begin 
    if ($1.atributo1 > $1.atributo2) then
        return 1;
    end if;
    return null;
end;
$$ 
language plpgsql;

Best Answer

In Postgres 8.4 or later you would use the USING clause of EXECUTE to pass values safely and efficiently. That's not available in your version 8.3, yet. In your version it could could work like this:

CREATE OR REPLACE FUNCTION test_conditions()
  RETURNS SETOF bigint AS
$func$
DECLARE
   _rec    record;
   _func   text;
   _result bigint;
BEGIN
   FOR _func in
      SELECT  p.proname
      FROM    pg_catalog.pg_namespace n
      JOIN    pg_catalog.pg_proc      p ON p.pronamespace = n.oid
      WHERE   n.nspname = 'operacional'
      AND     p.proname LIKE E'fn\\_condition\\_%'  -- no parens, proper string
      ORDER   BY p.proname  -- no parens
   LOOP
      FOR _rec in
         SELECT * FROM my_table WHERE id IN (1,2,3)  -- no parens needed
      LOOP
         EXECUTE 'SELECT ' || quote_ident(_func) || '(' || quote_literal(_rec) || ')'
         INTO _result;
         RETURN NEXT _result;
      END LOOP;
   END LOOP;
END
$func$  LANGUAGE plpgsql SET search_path = 'public';

Call:

SELECT * FROM test_conditions();

  • If you use set search_path = 'pg_catalog'; in the function body, then your table in the public schema is not visible any more. And it would be a very bad idea to globally SET the search path. The effect stays for the duration of the setting. You could use SET LOCAL to contain it to the transaction, but that would still be a bad idea. Instead, if you really need to, set the environment of the function only, like demonstrated.
    More about the search path in Postgres:

  • Just executing a SELECT without assigning or returning the result would be pointless. Use the INTO clause of EXECUTE and then RETURN NEXT. In modern Postgres you would replace the inner loop with RETURN QUERY EXECUTE.

  • Use quote_ident() and quote_literal() to escape identifiers and literals properly when building a dynamic query string. In modern Postgres you would use format().

  • It's not very efficient to cast the whole row to it's string representation, escape and cast back. This alternative approach has to read from the table repeatedly, but is cleaner otherwise (the row is passed as value directly):

      FOR i IN
     VALUES (1), (2), (3)
  LOOP
     EXECUTE 'SELECT ' || quote_ident(_func) || '(t) FROM my_table t WHERE id = ' || i
     INTO _result;
     RETURN NEXT _result;
  END LOOP;

Example function

You can also radically simplify your example function with this SQL function:

CREATE OR REPLACE FUNCTION fn_condition_1(p_record my_table)
  RETURNS bigint AS
$func$
   SELECT CASE WHEN $1.atributo1 > $1.atributo2 THEN bigint '1' END
$func$  LANGUAGE sql;
Related Question