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
