Pass ROWTYPE parameter with the EXECUTE command

Question

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

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

My difficulty is in how to pass the parameter correctly to the EXECUTE command.

How to indicate in the excerpt commented on the function below 'select ' || funcoes.proname || '(' || registro || ')'; that the input is of type my_table?

create or replace function testa_condicoes()
returns void as 
$$
declare
    registro minha_table%rowtype;
    funcoes pg_proc%rowtype;    
begin 
    set search_path = 'pg_catalog';

    for registro in (select * from minha_table where id in (1,2,3)) loop
        for funcoes 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_condicao_%')
            order by p.proname) 
        loop
            --execute 'select ' || funcoes.proname || '(' || registro || ')';
        end loop;
    end loop;
end;
$$ 
language plpgsql;

Role model

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

Answer 1

The main problem with your function is that you have to return a value row as in select my_table from my_table. I gave a simplified:

create or replace function test_conditions()
returns void as $$
declare
    r record;
begin 
    for r in 
        select my_table, p.proname
        from
            my_table
            cross join
            (
                select p.proname
                from
                    pg_namespace n
                    inner join
                    pg_proc p on p.pronamespace = n.oid
                where
                    n.nspname = 'operacional'
                    and
                    p.proname like ('fn_condition\\_%')
            ) p
        where my_table.id in (1,2,3)
    loop
        execute 'select ' || r.proname || '(' || replace(r.my_table, ',,', 'null') || '::mytable)';
    end loop;
end;
$$ language plpgsql;

Note that the query within the loop for has no parenthesis. pg_catalog is an identifier and the use of quotes in this case is deprecated. Anyway the scheme pg_catalog is always part of the search_path and it is not necessary to include it.

Answer 2

In higher versions the version 8.3 it is possible to use the expression USING to provide parameters. This version, however, does not support this syntax. To use parameters properly it is recommended to use the functions quote_ident(text) and quote_literal(value) as shown below:

execute 'select ' || quote_ident(funcao) || '(' || quote_literal(registro) || ')';

Solution

The function below posted as a solution requires some observations, as it differs in some points from the function posted as a doubt.

• The assignment of 'pg_catalog' to search_path is not necessary, because the scheme 'pg_catalog' is already included in the schemes searched in search_path.
• The '_' character used in the condition like must be escaped, for when not escaped represents a joker that matches any single character.

Function testa_condicoes:

create or replace function testa_condicoes()
    returns void as 
    $$
    declare
        registro minha_table%rowtype;
        id_condicao bigint;
        funcao text;    
    begin 
        set search_path = 'operacional';

        for registro in select * from minha_table where id in (1, 2, 3) loop
            for funcao 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\\_condicao\\_%')
                order by p.proname) 
            loop
                execute 'select ' || quote_ident(funcao) || '(' || quote_literal(registro) || ')' into id_condicao;
                raise info 'id_condicao: %', id_condicao;
            end loop;
        end loop;
    end;
    $$ 
    language plpgsql;

---

Source:

Executing Dynamic Commands
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html

Passing ROWTYPE to EXECUTE https://dba.stackexchange.com/questions/127001/passing-rowtype-parameter-to-execute