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?
