Re: Pl/Pgsql triger procedure calls with parameters

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 passing one. You'll need to use the special variables TG_NARGS and TG_ARGV[]. See section 24.3, in the documentation.

Regards,

Antonio Sergio