On 2/8/16, Johannes <jotpe@xxxxxxxxx> wrote: > Hi, > > is there a best practice to share data between two select statements? > > Imaging following situation: I want to receive two result sets from two > tables, referring to a specific id from table t0 AND I try not to query > for that specific id a second time. > > Table t0 returns 1 row and table t1 returns multiple rows. > > begin; > select id, col1, col2, ... from t0 where id = (select max(id) from t0 > where col1 = value1 and col2 = value2 and ...); > select col1 from t1 where t0_id = (select max(id) from t0 where col1 = > value1 and col2 = value2 and ...); > commit; > > Best regards Johannes Yes. You can use temporary autodeleting tables[1] for that. Similar to: BEGIN; CREATE TEMPORARY TABLE temptable(id int) ON COMMIT DROP; INSERT INTO temptable SELECT max(id) FROM t0 WHERE col1 = value1 and col2 = value2 and ...; SELECT id, col1, col2, ... FROM t0 INNER NATURAL JOIN temptable; SELECT col1 FROM t1 INNER JOIN temptable ON (t0_id = temptable.id); COMMIT; [1]http://www.postgresql.org/docs/9.5/static/sql-createtable.html -- Best regards, Vitaly Burovoy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
