Re: Method to pass data between queries in a multi-statement transaction

On Wed, Apr 17, 2019 at 5:30 PM Souvik Bhattacherjee <kivuosb(at)gmail(dot)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(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>>
>> On Wed, Apr 17, 2019 at 3:04 PM Souvik Bhattacherjee <kivuosb(at)gmail(dot)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.
>>
>