On Thursday 17 September 2009 8:35:52 am Nico Mandery wrote: > Hello list, > > I am trying to wirte a rule which calls a PLPgSQL-function upon an > Insert in a table. Here is a somewhat simplified example of what i got > so far: > > CREATE TABLE mytable ( > mytable_id serial PRIMARY KEY, > something text > ); > > > CREATE OR REPLACE FUNCTION _rule_insert_my(something text) > RETURNS integer AS > $BODY$ > BEGIN > -- do something > return mytable_id; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE > COST 100; > > > CREATE OR REPLACE RULE _insert AS > ON INSERT TO mytable DO INSTEAD SELECT > _rule_insert_my(new.something) AS mytable_id; > > > So far this works quite well. But I got a few situations where I need to > do a query which uses RETURNING to get the value of the newly generated > primary key. Like this one: > > INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id; > > This breaks because I did not specify a RETURNING-Clause in the rule. > But how can specify RETURNING with SELECT? > > > Thank your in advance for your help. > > regards, > nico > > -- > Nico Mandery
I am going to assume that '--do something' is more complicated then getting the mytable_id. If that is the case why not create an INSERT function/trigger that does the 'something' and then just do: INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id; -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql