Sample Header Ad - 728x90

Passing ROWTYPE parameter to EXECUTE

5 votes
1 answer
4422 views
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;
Asked by Geison Santos (133 rep)
Jan 22, 2016, 03:26 PM
Last activity: Nov 18, 2022, 12:18 AM