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