Access NEW and OLD from function called by a rule

[Frodo Larik <lists@xxxxxxxxx>]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org