Pl/Pgsql triger procedure calls with parameters

From: David A Dickson <davidd(at)saraswati(dot)wcg(dot)mcgill(dot)ca>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Pl/Pgsql triger procedure calls with parameters
Date: 2001-11-26 22:15:57
Message-ID: Pine.LNX.4.33.0111261652100.20572-100000@blues.wcg.mcgill.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message news.postgresql.org 2001-11-26 23:36:28 check constriaint for BLOB
Previous Message Gregory Wood 2001-11-26 22:09:30 Re: Optimize for insertions?