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