On Wed, Apr 17, 2019 at 5:30 PM Souvik Bhattacherjee <kivu...@gmail.com> wrote:
> > What if I need the result of the join to be stored into table3 as well > as the tuples that participated in the query to be deleted from table1. The > following can be done without the need to transfer values from the previous > query into the next: > > begin; > insert into table3 (id, attr1, attr2) (select t1.cid, t1.empname, t2.dept > from table1 t1, table2 t2 where t1.cid = t2.cid); > delete from table1 where cid in (select c.cid from table1 t1, table2 t2 > where t1.cid = t2.cid); > commit; > > You can use INSERT...RETURNING in a WITH query: postgres=# create table foo (a integer); CREATE TABLE postgres=# create table bar (b integer); CREATE TABLE postgres=# insert into bar values (42); INSERT 0 1 postgres=# with i as (insert into foo values (42) returning a) delete from bar where b = (select a from i); DELETE 1 -Michel > > On Wed, Apr 17, 2019 at 6:16 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> >> On Wed, Apr 17, 2019 at 3:04 PM Souvik Bhattacherjee <kivu...@gmail.com> >> wrote: >> >>> Hello, >>> >>> I'm trying to pass some values between queries in a multi-statement >>> transaction. For example, consider the following representative >>> multi-statement transaction: >>> >>> begin; >>> select * from table1 t1, table2 t2 where t1.cid = t2.cid; >>> delete from table1 where cid in >>> (values-to-be-populated-from-the-previous-query); >>> commit; >>> >> >> There are few if any situations where you need to immediately and >> completely pass all values from one query to another in the same >> transaction where the queries cannot just be combined into a single >> statement. Your representative example is one that is easily combined into >> a single statement. >> >> Now, the stuff you are trying seems to indicate you are trying to do >> something in C, inside the engine itself, with all of this. If that is the >> case you may want to be more clear as to what you are attempting to do. >> But as far as server SQL goes the only persistence area are >> tables/relations - including temporary ones. >> >> David J. >> >
