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

Hi Tom,

Tom Lane wrote:

>Frodo Larik <lists(at)elasto(dot)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=#
>
>
Thanks for tips! It works, but it seems I have to rewrite this function
for every rule??

I wanted to make te function more generic,after doing this I understand
that the argument of insert_person(workers) is a table/view name:

test_db=# CREATE OR REPLACE FUNCTION insert_person(persons) RETURNS
integer AS '
test_db'# BEGIN
test_db'# INSERT INTO persons ( first_name, last_name )
test_db'# VALUES ( $1.first_name, $1.last_name );
test_db'# RETURN currval(''persons_id_seq'');
test_db'# END
test_db'# ' LANGUAGE 'plpgsql';
CREATE FUNCTION
test_db=#
test_db=# CREATE OR REPLACE RULE insert_worker AS ON INSERT TO workers
DO INSTEAD (
test_db(# SELECT insert_person(new.*) AS person_id;
test_db(#
test_db(# INSERT INTO t_workers ( person_id, client_id )
test_db(# VALUES ( currval('persons_id_seq'), NEW.client_id );
test_db(# );
ERROR: function insert_person(workers) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.

That means I have to create functions like insert_person(workers) ,
insert_person(othertable) and function insert_person(anothertable).

Suggestions?

Sincerely,

Frodo Larik