From: | Souvik Bhattacherjee <kivuosb(at)gmail(dot)com> |
---|---|
To: | Michel Pelletier <pelletier(dot)michel(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 16:02:57 |
Message-ID: | CAANrPSfsC5XpnDPEfsu6kjxAR1H4mh5rGYqMN=qCOztdGs0mTw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks Michel.
However this only works if a is an unique attribute in the table that would
help us to identify tuples that participated in the join. Consider the
following join:
insert into table3 (id, level, empname, salary)
(select t0.cid, t0.level, t1.empname, t2.salary from table0 t0, table1 t1,
table2 t2 where t0.cid = t1.cid and t1.pid = t2.pid);
Now if I want to delete those tuples from table2 that satisfied the join
condition, I need to execute the join again with additional attributes.
Also note that based on query plan, i.e. whether table0 and table1 were
joined first followed by table1 and table2, we have to execute one
additional join to get the tuples in table2 that satisfied the join
condition (t1.pid = t2.pid).
Getting that information while the query is executed may not be difficult.
There are other use cases in my application that require me to transfer the
data from one query to the next within a transaction.
Thus, what I'm looking for here is way to store the information and then
pass that information to the next query efficiently.
For example, is it possible to define a struct of my choice, private to the
current transaction, that would store the data and then pass it around to
the next query in the transaction without having to materialize that struct
(or deal with concurrency issues as in the hash table approach mentioned
earlier) .
-SB
On Wed, Apr 17, 2019 at 10:47 PM Michel Pelletier <
pelletier(dot)michel(at)gmail(dot)com> wrote:
> 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 | Souvik Bhattacherjee | 2019-04-18 16:03:22 | Re: Method to pass data between queries in a multi-statement transaction |
Previous Message | Adrian Klaver | 2019-04-18 14:49:56 | Re: Method to pass data between queries in a multi-statement transaction |