[SQL] Thank you all for your help...
I appreciate very much your opinions. They'll help me to adjust my approach to this problem. As I meant, it wouldn't be "cool" to have hibernate hoarding 50 ids, I didn't mean the id-gaps and id-loss, that's no problem, but also to lose the timely clear id alignment in the case of parallel access of the database with another application... Sure, I'll have timestamps vor that, but... :) Finally I'll go - certainly - with one sequence per PK. Thanks Kind regards, Rawi -- View this message in context: http://www.nabble.com/Hibernate%2C-web-application-and-only-one-sequence-for-all-primary-keys-tp25490498p25504364.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rules, functions and RETURNING
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
