Re: [PGSQL 8.2.x] INSERT+INSERT

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