Re: Method to pass data between queries in a multi-statement transaction - 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