Message-ID: <4AB39E5D.9030600@geops.de>
Date: Fri, 18 Sep 2009 16:51:09 +0200
From: Nico Mandery <nico.mandery@geops.de>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Rules, functions and RETURNING
References: <4AB25758.60702@geops.de> <200909171940.06313.aklaver@comcast.net>
In-Reply-To: <200909171940.06313.aklaver@comcast.net>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

Adrian Klaver wrote:
> 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,

I just rewrote a few queries to use the function directly instead of an
insert. But a trigger which fires before the actual INSERT or UPDATE is
a good suggestion. In the case I do not want any data to be inserted in
the table, returning NULL from the trigger should  do the trick.

thanks a lot.

regards,
nico