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

On Thu, Apr 18, 2019 at 9:03 AM Souvik Bhattacherjee <kivuosb(at)gmail(dot)com>
wrote:

> 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.
>

Or just add pid to table3...

> 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.
>

There may be some that benefit to some degree but its likely that you can
write the application and queries in such a way to avoid a hard requirement.

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) .
>

How much development and maintenance effort are you willing to spend here
to gain what is likely to amount to only a bit of efficiency? Many things
are possible if you are going to modify the server code but why add grief?

David J.