Pass parameter of type ROWTYPE with the EXECUTE command

Navigation
6

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

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

My problem is how to pass the parameter correctly to the EXECUTE command.

How to indicate in commented out in the function below 'select ' || funcoes.proname || '(' || registro || ')'; that the entry 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;

Example of functions 

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;
    
asked by anonymous 21.01.2016 / 19:14

2 answers

0

In versions above the 8.3 version you can use the expression USING to provide parameters. This version, however, does not support this syntax. To use parameters appropriately, it is recommended to use the functions quote_ident(text) and quote_literal(value) as exemplified below: 

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

Solution

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

  • Assigning 'pg_catalog' to search_path not needed , because the 'pg_catalog' schema is already included in the schemas searched in search_path.
  • The '_' character used in the like condition must be escaped, because when not escaped it represents a joker marrying any single character .

Function tests_conditions: 

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
link

Passing ROWTYPE parameter to EXECUTE link

    
01.02.2016 / 18:23
0

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

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 inside the for loop has no parentheses. pg_catalog is an identifier and the use of quotation marks in this case is deprecated. Anyway the pg_catalog schema is always part of search_path and it is not necessary to include it.

    
22.01.2016 / 20:05
How to combine two vectors and get all results from a given account with them How do I configure crontab to run URL, Linux ubuntu command