Re: Method to pass data between queries in a multi-statementtransaction - Mailing list pgsql-general

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@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
> 
>     On Wed, Apr 17, 2019 at 3:04 PM Souvik Bhattacherjee
>     <kivuosb@gmail.com <mailto:kivuosb@gmail.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.klaver@aklaver.com