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

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(dot)g(dot)johnston(at)gmail(dot)com <mailto: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 <mailto: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.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com