Richard Huxton wrote:
> sad wrote:
>> Richard Huxton wrote:
>>
>> Then this is the question on the execution order of the statement
>> INSERT...SELECT...
>
> You'll want "Overview of PostgreSQL internals" then
>
> http://www.postgresql.org/docs/8.2/static/overview.html
>
>>> What do you think should happen?
>>
>> I had expected all the currval() calls to be called before all the
>> triggers fired.
>
> However, consider the case where your SELECT generated 100,000,000 rows
> but had an unacceptable value in the second row. If you assembled the
> result-set first then you'd have to store all those rows just to fail on
> the second one.
>
> In practice, I suspect it works this way because the planner / executor
> arranges things in this manner for SELECT statements (so you can e.g.
> stop early with a LIMIT clause).
It is clear. Thnx.
> However, relying on a specific order of execution (unless it's defined
> in the SQL standard somewhere) is probably unwise. A future optimisation
> might make your assumptions wrong.
That's why i'd post the question !
Trying to know if this behavior finally defined and documented.
>
> Can I ask what you were trying to achieve with the currval() select +
> nextval() trigger combination. I've not seen that pattern before.
>
I'll try to describe...
There is the global ttt_id_seq for the globally unique ids for all the
tables. Since all those table are inherit from one ancestor.
CREATE TABLE ttt1 (
id int primary key,
info text);
CREATE TABLE ttt (
id int primary key,
a int references ttt1(id),
info text);
CREATE TABLE ttt2 (
id int primary key,
info text);
id default value is always set by the trigger before insert on each
table for each row.
The particular subproblem is to
insert one record into ttt1
and then insert corresponding record into ttt,
___This is the place to use currval.
using some data from a ttt2
___This is a place to INSERT...SELECT... from ttt2;
This works while SELECT FROM ttt2 returns exactly one row satisfying my
needs.
Finally it looks like:
BEGIN; INSERT INTO ttt1 (....) VALUES (....); INSERT INTO ttt (a,info) SELECT currval('ttt_id_seq'), foo(info) FROM
ttt2WHERE ....;
END;
P.S.
This happened because i am constantly trying to avoid procedural code
where possible to code SQL entirely.