Skip site navigation (1) Skip section navigation (2)

Re: currval() within one statement

From: silly_sad <sad(at)bankir(dot)ru>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: currval() within one statement
Date: 2008-01-22 11:27:29
Message-ID: 4795D321.5050409@bankir.ru (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-sql
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.



In response to

Responses

pgsql-sql by date

Next:From: hubert depesz lubaczewskiDate: 2008-01-22 11:27:54
Subject: Re: currval() within one statement
Previous:From: Richard HuxtonDate: 2008-01-22 10:39:36
Subject: Re: currval() within one statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group