From: | Frodo Larik <lists(at)elasto(dot)nl> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Access NEW and OLD from function called by a rule |
Date: | 2005-08-12 09:20:54 |
Message-ID: | 42FC69F6.3090200@elasto.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ronzani Dario | 2005-08-12 10:33:23 | Linux Postgres authentication against active directory |
Previous Message | Richard Huxton | 2005-08-12 08:41:55 | Re: OTICE: adding missing FROM-clause entry for table |