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

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