From: Nico Mandery <nico.mandery@geops.de>
To: pgsql-sql@postgresql.org
Subject: Rules, functions and RETURNING
Date: Thu, 17 Sep 2009 17:35:52 +0200
Message-ID: <4AB25758.60702@geops.de> (raw)
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
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: nico.mandery@geops.de
Subject: Re: Rules, functions and RETURNING
In-Reply-To: <4AB25758.60702@geops.de>
* 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