On 4/17/19 5:30 PM, Souvik Bhattacherjee wrote:
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.
> 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);
Well the DELETE is not going to work as c.cid will error as undefined.
commit;
However note that we have to perform the join twice, which is not
efficient. Now to make things worse, increase the number of tables to
join while imposing the requirement of tuple deletion to apply to all or
to a subset of the tables that participate in join.
You might want to take a look at CTE's:
https://www.postgresql.org/docs/11/queries-with.html
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.
I'm trying to modify the engine here.
-SB
On Wed, Apr 17, 2019 at 6:16 PM David G. Johnston
<david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote:
On Wed, Apr 17, 2019 at 3:04 PM Souvik Bhattacherjee
<kivuosb@xxxxxxxxx <mailto:kivuosb@xxxxxxxxx>> 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.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
