Passing ROWTYPE parameter on EXECUTE

Question

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 comun prefix 'fn_condicao_' and receives as a parameter an object of type 'my_table'.

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_condicao_' and dynamically execute them with EXECUTE.

My problem is how to pass the correct shape 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;

Answer 1

I believe you issue is that the execute command in your function is attempting to interpolate the value of v_record which essentially turns it into a discrete list of parameters rather than the native row type that the function is expecting.

If you are willing to change the parameter types of each of the functions, that might be the easiest way to handle this. If not, then you need some way to pass the native row type to the dynamic function call. As bad as this looks, I think something like this would work:

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 '
            do $ZOOM$
              declare
                v_rec my_table%rowtype;
              begin
                select *
                into v_rec
                from my_table
                where id = ' || v_record.id || ';

                perform ' || func_name || '(v_rec);
              end;
            $ZOOM$
          ';
        end loop;
    end loop;
end;
$$ 

Also, I think you will need to change your select to a perform (as above)... either that or do a select into.

Answer 2

This question was answered in DataBase Administrators by Erwin Brandstetter. So, I would like to share with you the resolution.

---

DataBase Administrators 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:

  • How does the search_path influence identifier resolution and the “current schema”

• 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;