From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Re: Rules, functions and RETURNING
Date: Thu, 17 Sep 2009 19:40:05 -0700
User-Agent: KMail/1.9.10
References: <4AB25758.60702@geops.de>
In-Reply-To: <4AB25758.60702@geops.de>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200909171940.06313.aklaver@comcast.net>

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
aklaver@comcast.net