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 ttt2 WHERE ....;
END;
P.S.
This happened because i am constantly trying to avoid procedural code
where possible to code SQL entirely.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
