pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

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
Message-ID: <200909171940.06313.aklaver@comcast.net> (raw)
In-Reply-To: <4AB25758.60702@geops.de>
References: <4AB25758.60702@geops.de>

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

view thread (3+ messages)  latest in thread

Message-ID: <200909171940.06313.aklaver@comcast.net>
Permalink:  ../200909171940.06313.aklaver@comcast.net/
Also on:    postgresql.org/message-id/200909171940.06313.aklaver@comcast.net

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: aklaver@comcast.net
  Subject: Re: Rules, functions and RETURNING
  In-Reply-To: <200909171940.06313.aklaver@comcast.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox