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?
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? |