pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRules, functions and RETURNING
3+ messages / 2 participants
[nested] [flat]
* Rules, functions and RETURNING
@ 2009-09-17 15:35 Nico Mandery <nico.mandery@geops.de>
2009-09-18 02:40 ` Re: Rules, functions and RETURNING Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 3+ messages in thread
From: Nico Mandery @ 2009-09-17 15:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rules, functions and RETURNING
2009-09-17 15:35 Rules, functions and RETURNING Nico Mandery <nico.mandery@geops.de>
@ 2009-09-18 02:40 ` Adrian Klaver <aklaver@comcast.net>
2009-09-18 14:51 ` Re: Rules, functions and RETURNING Nico Mandery <nico.mandery@geops.de>
0 siblings, 1 reply; 3+ messages in thread
From: Adrian Klaver @ 2009-09-18 02:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Rules, functions and RETURNING
2009-09-17 15:35 Rules, functions and RETURNING Nico Mandery <nico.mandery@geops.de>
2009-09-18 02:40 ` Re: Rules, functions and RETURNING Adrian Klaver <aklaver@comcast.net>
@ 2009-09-18 14:51 ` Nico Mandery <nico.mandery@geops.de>
0 siblings, 0 replies; 3+ messages in thread
From: Nico Mandery @ 2009-09-18 14:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-09-18 14:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-17 15:35 Rules, functions and RETURNING Nico Mandery <nico.mandery@geops.de>
2009-09-18 02:40 ` Adrian Klaver <aklaver@comcast.net>
2009-09-18 14:51 ` Nico Mandery <nico.mandery@geops.de>
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