Message-ID: <4AB25758.60702@geops.de>
Date: Thu, 17 Sep 2009 17:35:52 +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: Rules, functions and RETURNING
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

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