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 по дате отправления:

Предыдущее
От: Jeff Davis
Дата:
Сообщение: Re: Optimize for insertions?
Следующее
От: wsheldah@lexmark.com
Дата:
Сообщение: Re: Optimize for insertions?