Thread: Pl/Pgsql triger procedure calls with parameters

I am trying to make a call to a function that takes three text parameters
as input from a trigger. The function is supposed to check if
SELECT * FROM $3 WHERE new.$1 = $3.$2
has more than 0 rows. If it does then new is returned, if not an exception
is raised. My problem is that I get an error every time I try to declare a
trigger that calls this function. Below is the code for the function and
trigger I am trying to create.

CREATE FUNCTION validate_field(text, text, text)
RETURNS opaque
        AS 'DECLARE
                input new.$1;
                static ALIAS $2;
                table ALIAS $3;
                data_rec RECORD;
        BEGIN
                SELECT INTO data_rec *
                        FROM table
                        WHERE static = input;
                IF NOT FOUND
                        THEN RAISE EXCEPTION ''Input value not valid'';
                        RETURN new;
                END IF;
        END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_name
BEFORE INSERT OR UPDATE
ON table1
FOR EACH ROW
EXECUTE PROCEDURE validate_field('field1', 'field2', 'table2');

CREATE
psql:validate_field.txt:24: ERROR:  parser: parse error at or near "field1"

It would save me many lines of code if I could call the function from
the trigger since I need to do it for many combinations of table1,
field1, field2, and table2. Any ideas on how to make this work?

David A Dickson <davidd@saraswati.wcg.mcgill.ca> writes:
> I am trying to make a call to a function that takes three text parameters
> as input from a trigger. The function is supposed to check if
> SELECT * FROM $3 WHERE new.$1 = $3.$2
> has more than 0 rows.

While this may seem neat and clean, I think you'd be a lot better off
writing multiple trigger functions that have the table and field names
wired into them.  If you insist on parameterizing like this, you will
have to run the queries via EXECUTE in plpgsql, which means you will get
no query plan caching, which is a performance hit you probably do not
want to take in a trigger function.

            regards, tom lane

David A Dickson wrote:

>
>I am trying to make a call to a function that takes three text parameters
>as input from a trigger. The function is supposed to check if
>SELECT * FROM $3 WHERE new.$1 = $3.$2
>has more than 0 rows. If it does then new is returned, if not an exception
>is raised. My problem is that I get an error every time I try to declare a
>trigger that calls this function. Below is the code for the function and
>trigger I am trying to create.
>
>CREATE FUNCTION validate_field(text, text, text)
>RETURNS opaque
>
...

>
>LANGUAGE 'plpgsql';
>
>CREATE TRIGGER trigger_name
>BEFORE INSERT OR UPDATE
>ON table1
>FOR EACH ROW
>EXECUTE PROCEDURE validate_field('field1', 'field2', 'table2');
>
>CREATE
>psql:validate_field.txt:24: ERROR:  parser: parse error at or near "field1"
>
>It would save me many lines of code if I could call the function from
>the trigger since I need to do it for many combinations of table1,
>field1, field2, and table2. Any ideas on how to make this work?
>

Hi,

Trigger procedures take the arguments passed at create trigger time via a different mechanism that the normal parameter
passingone. You'll need to use the special variables TG_NARGS and TG_ARGV[]. See section 24.3, in the documentation. 

Regards,

Antonio Sergio