Hi Tom,
Tom Lane wrote:
Frodo Larik <lists@xxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
