Re: [PGSQL 8.2.x] INSERT+INSERT

On Thursday 21 June 2007 15:47:17 Dawid Kuroczko wrote:
> On 6/21/07, Vincenzo Romano <vincenzo(dot)romano(at)gmail(dot)com> wrote:
> > Hi all.
> > I'd like to do the following:
> >
> > insert into t1
> > values (
> > 'atextvalue',(
> > insert into t2
> > values ( 'somethingelse' )
> > returning theserial
> > )
> > )
> > ;
> >
> > that is, I first insert data into t2 getting back the newly
> > created serial values, then i insert this values in another
> > table. I get an error message:
> > ERROR: syntax error at or near "into"
> > referring to thwe second inner "into".
> > Is there a way to do this?
> > The inner insert...returning should be the "expression" to be
> > used in the outer insert.
> > My objective i"Dawid Kuroczko" <qnex42(at)gmail(dot)com>s to create an
SQL script to load some 20+ million
> > records and avoiding function calls would save some time.
>
> I'm afraid INSERT ... RETURNING cannot be used where a (sub)select
> could be. It returns data to the calling application only.

I think it would be greatly helpful if the insert...returning could be
seen as a "select" statement and, thus, being usable in the way I
have described. I suspect that the insert...returning is actually
implemented as an inser plus a select.

>
> Given tables:
>
> qnex=# CREATE TABLE t1 (t text, id int);
> qnex=# CREATE TABLE t2 (id serial, sth text);
> NOTICE: CREATE TABLE will create implicit sequence "t2_id_seq" for
> serial column "t2.id"
>
> You want to:
>
> qnex=# INSERT INTO t2 (sth) VALUES ('somethingelse');
> INSERT 0 1
> qnex=# INSERT INTO t1 VALUES ('atextvalue', currval('t2_id_seq'));
> INSERT 0 1

This works only when you execute statements one by one like you did.

>
> Or wrap it around SQL function:

That's the way I'm doind now even if in a slightly different way.
20+ million calls will badly slooow down the DB insertions and require
you to know the exact name of the implicit sequence and, more
important, not to have any concurrent accesses to it.

--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]