Pl/Pgsql triger procedure calls with parameters
От | David A Dickson |
---|---|
Тема | Pl/Pgsql triger procedure calls with parameters |
Дата | |
Msg-id | Pine.LNX.4.33.0111261652100.20572-100000@blues.wcg.mcgill.ca обсуждение исходный текст |
Ответы |
Re: Pl/Pgsql triger procedure calls with parameters
|
Список | 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?
В списке pgsql-general по дате отправления: