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

Well the DELETE is not going to work as c.cid will error as undefined.

> Yes, that's a typo. I haven't tested it out before typing; just wanted to
convey the general idea.

-SB

On Thu, Apr 18, 2019 at 10:50 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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(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
>