Re: Access NEW and OLD from function called by a rule

Frodo Larik <lists@elasto.nl> writes:
> PostgreSQL obviously complains about NEW not available, how can I make
> it available? Is this the way to do it?

No.  You seem to have read something about trigger functions, but this
usage is not a trigger function.  You need to do it more like this:

regression=# CREATE OR REPLACE FUNCTION insert_person(workers) returns void as $$
regression$# begin
regression$#   INSERT INTO persons ( first_name, last_name )
regression$#      VALUES ( $1.first_name, $1.last_name );
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers DO INSTEAD (
regression(# SELECT insert_person(new.*);
regression(# INSERT INTO t_workers ( person_id, client_id )
regression(# VALUES ( currval('persons_id_seq'), NEW.client_id );
regression(# );
CREATE RULE
regression=# insert into workers ( first_name, last_name ) VALUES ( 'John', 'Doe');
 insert_person
---------------

(1 row)

regression=#

The extra SELECT result is a bit annoying --- you could maybe hide that
by invoking the function within the rule INSERT, say by having it return
the inserted persons id.

I think passing "new.*" to a function from a rule works since about 7.4
or so.

            regards, tom lane