From: | Michel Pelletier <pelletier(dot)michel(at)gmail(dot)com> |
---|---|
To: | Souvik Bhattacherjee <kivuosb(at)gmail(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Method to pass data between queries in a multi-statement transaction |
Date: | 2019-04-18 02:47:15 |
Message-ID: | CACxu=vJOmBgU8r+Vi6CE+kJuhscpz_Odn+7TFWg04a__7QQMfg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ajay Pratap | 2019-04-18 02:47:47 | Re: PostgreSQL ping/pong to client |
Previous Message | pabloa98 | 2019-04-18 02:07:57 | Re: is it possible to create partitioned tables using tables from different schemas |