Well the DELETE is not going to work as c.cid will error as undefined.
-SB
On Thu, Apr 18, 2019 at 10:50 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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
