Access NEW and OLD from function called by a rule
От | Frodo Larik |
---|---|
Тема | Access NEW and OLD from function called by a rule |
Дата | |
Msg-id | 42FC69F6.3090200@elasto.nl обсуждение исходный текст |
Ответы |
Linux Postgres authentication against active directory
Re: Access NEW and OLD from function called by a rule |
Список | pgsql-general |
Hello, I was wondering if it was possible to get a hold of the NEW and OLD variables available in a Rule and pass them to a function? Maybe there is another (better) way of accomplishing what I try to do, so I'll sketch you my testing layout: CREATE TABLE clients ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE persons ( id SERIAL PRIMARY KEY, first_name text, last_name text ); CREATE TABLE t_workers ( id SERIAL PRIMARY KEY, person_id integer REFERENCES persons(id), client_id integer REFERENCES clients(id) ); CREATE TABLE t_contacts ( id SERIAL PRIMARY KEY, person_id integer REFERENCES persons(id), client_id integer REFERENCES clients(id) ); -- view containing all worker data CREATE VIEW workers AS SELECT w.*, p.first_name, p.last_name FROM t_workers AS w INNER JOIN persons AS p ON ( w.person_id = p.id ); Now for inserting data in the workers view I created a rule: CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD ( INSERT INTO persons ( first_name, last_name ) VALUES ( NEW.first_name, NEW.last_name ); INSERT INTO t_workers ( person_id, client_id ) VALUES ( currval('persons_id_seq'), NEW.client_id ); ); This works. Then I also have a t_contacts table where I want do the same with, I create a view called contacts and a rule called insert_contact. Later on I will be having more views containing data from persons. So I thought I could make some kind of macro of the "INSERT INTO persons .." part. I saw PostgreSQL has support for CREATE FUNCTION (something I'm not really familiar with). This is what I wanted to do: I create a FUNCTION to insert data into persons: CREATE OR REPLACE FUNCTION insert_person() RETURNS OPAQUE AS ' BEGIN INSERT INTO persons ( first_name, last_name ) VALUES ( NEW.first_name, NEW.last_name ); RETURN NULL; END ' LANGUAGE 'plpgsql'; And I will call the FUNCTION from the isnert_worker RULE CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD ( SELECT insert_person(); INSERT INTO t_workers ( person_id, client_id ) VALUES ( currval('persons_id_seq'), NEW.client_id ); ); If I try to insert data into workers, the following happens: test_db=# insert into workers ( first_name, last_name ) VALUES ( 'John', 'Doe'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function "insert_person" line 2 at SQL statement PostgreSQL obviously complains about NEW not available, how can I make it available? Is this the way to do it? Sincerely, Frodo Larik
В списке pgsql-general по дате отправления: