use RETURNING-data in another statement

Question

Hello,

I want to use data 'theID' from a PostgreSQL-RETURNING statement

INSERT INTO ... RETURNING theID;

further in another statement. How can this be done (without cursors)?

My full statement (copying some data and delete them from source table; tab1 & tab2 definition are equal):

DELETE FROM tab1 WHERE tab1id IN 
  (
  INSERT INTO tab2 
    SELECT * FROM tab1 WHERE ?something? 
  RETURNING tab2id
  );

The error message:

ERROR:  Syntaxerror near »INTO«
LINE 7: DELETE FROM tab1 WHERE tab1id IN (INSERT INTO tab2 SELECT * FROM ...
                                                  ^

Thanks, Tebas.

Answer 1

No you can't, it's not yet supported. Use a stored procedure or your application to fix your problem.

Answer 2

You cannot use DML statements in this location, unfortunately. You need to store the data somewhere else. (Work is being done on getting the ability to use DML like this into PostgreSQL 9.1, but it's not there now).

For now, you can store the output of the INSERT.. RETURNING into a temporary table, and then use that one in your DELETE - just remember to wrap it in an explicit transaction...