5

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;
  • set search_path = 'pg_catalog'; in the function body is almost certainly wrong (in multiple ways). What is the schema of my_table? – Erwin Brandstetter Jan 22 '16 at 17:43
  • I know this does not answer the question, but please consider upgrading your DB - it is currently 7 versions behind. – dezso Jan 22 '16 at 17:44
  • The whole idea behind this approach is .. dubious. You are wide open to SQL injection. The procedure seems very inefficient. There is probably a better approach. – Erwin Brandstetter Jan 22 '16 at 17:49
  • The schema of table my_table is public. This example is only ilustration of my real problem. My single problem in this scope is to pass the rowtype object for a function fn_condition_1. @ErwinBrandstetter – Geison Santos Jan 22 '16 at 18:47
  • And you are serious about Postgres 8.3? It's not a typo and you mean 9.3 by any chance? – Erwin Brandstetter Jan 22 '16 at 19:07
4

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;
  • 1
    Erwin, thank you for your answer. It worked perfectly. Moreover I learned other things. Thank you again. – Geison Santos Jan 26 '16 at 18:01
  • 1
    thanks, i had similar problem because of these types record and rowtype, and your answer fix my problem (version 9.6) – HudsonPH Mar 20 '18 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.