Thread: [PGSQL 8.2.x] INSERT+INSERT

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 is to create an SQL script to load some 20+ million
records and avoiding function calls would save some time.

Thanks in advance.

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

On 6/21/07, Vincenzo Romano <vincenzo.romano@gmail.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 is 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.

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

Or wrap it around SQL function:

qnex=# CREATE OR REPLACE FUNCTION t_insert(sth text, t text) RETURNS VOID AS $$
  INSERT INTO t2 (sth) VALUES ($1);
  INSERT INTO t1 (t,id) VALUES ($2, currval('t2_id_seq'));
$$ LANGUAGE SQL;
CREATE FUNCTION
qnex=# SELECT t_insert('foo', 'bar');

...which should be inlined nicely, without PL/PgSQL overhead.

   Regards,
     Dawid

On Thursday 21 June 2007 15:47:17 Dawid Kuroczko wrote:
> On 6/21/07, Vincenzo Romano <vincenzo.romano@gmail.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@gmail.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]

> 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.

    You can create a function which does the INSERT RETURNING and returns the
result, and use this function in a subsequent INSERT.

    You can also write, in plpgsql :

    FOR a,b,c IN SELECT ... LOOP
        INSERT INTO table1 (...) VALUES (a,b) RETURNING your_id INTO _var;
        INSERT INTO table2 (...) VALUES (c , _var );
    END LOOP

"Vincenzo Romano" <vincenzo.romano@gmail.com> writes:

>> 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

Are you sure? How much?

> and require you to know the exact name of the implicit sequence

If you're sure you only have a single sequence being bumped here (no triggers
inserting somewhere else, or other columns with sequences for default values,
etc) then you could use lastval().

> and, more important, not to have any concurrent accesses to it.

huh? no, sequences don't require this. They would be pointless if they did.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com