Hi,
here's a short example, the problem is at the end:
DROP TABLE IF EXISTS foo;
CREATE TABLE foo ( id SERIAL PRIMARY KEY, c2 VARCHAR NOT NULL );
INSERT INTO foo VALUES ( DEFAULT, 'foo' );
INSERT INTO foo VALUES ( DEFAULT, 'bar' );
INSERT INTO foo VALUES ( DEFAULT, 'baz' );
DROP TABLE IF EXISTS bar;
CREATE TABLE bar ( id SERIAL PRIMARY KEY, c2 VARCHAR NOT NULL );
INSERT INTO bar VALUES ( DEFAULT, 'bla' );
INSERT INTO bar VALUES ( 4, 'blubb' );
SELECT * FROM foo;
SELECT * FROM bar;
-- This works
BEGIN;
UPDATE foo SET c2=bar.c2 FROM bar WHERE foo.id=bar.id RETURNING bar.id;
INSERT INTO foo (
SELECT * FROM bar WHERE id NOT IN (
SELECT id FROM foo
)
);
COMMIT;
-- This doesn't:
INSERT INTO foo (
SELECT * FROM bar WHERE id NOT IN (
UPDATE foo SET c2=bar.c2 FROM bar WHERE foo.id=bar.id RETURNING
bar.id
)
);
Question: Why is the last command not accepted? (ERROR: syntax error at
or near "foo" (the "foo" after UPATE)) Shouldn't the result structure of
"UPDATE ... RETURNING ..." be the same as the result structure from
"SELECT id FROM foo" (note: structure, not content in the example
above)?
Regards, Daniel
