On 2/9/16, Marc Mamin <M.Mamin@xxxxxxxxxxxx> wrote: > >>>>> Hi, >>>>> >>>>> is there a best practice to share data between two select statements? > > Hi, > I didn't check the whole thread Try it[1]. The thread is not so long (21 letters before yours) and it worth it. > so forgive me if this was already proposed, > but maybe you could do something like: > > create temp table result2 (...) > > query_1: > WITH cte as (select ..), > tmp as ( INSERT INTO result2 select ... from cte), > SELECT ... from cte; > > query_2: > select * from result2; There is a mistake here: query2 returns the same result as the query_1. > > regards, > Marc Mamin It is similar to the fourth answer[2] in the thread. If you are able to create temporary table with all fields of the first result only for avoiding one statement, it can be rewritten without CTE and one INNER JOIN (five statements): BEGIN; CREATE TEMPORARY TABLE temptable(id ..., col1 ..., col2 ..., ...) ON COMMIT DROP; INSERT INTO temptable SELECT id, col1, col2, ... FROM t0 WHERE col1 = value1 and col2 = value2 and ... RETURNING *; SELECT col1 FROM t1 INNER JOIN temptable ON (t0_id = temptable.id); COMMIT; === If it is hard to detect (or just lazy to write) what types temporary table has, you can rewrite it as (also five statements): BEGIN; CREATE TEMPORARY TABLE temptable ON COMMIT DROP AS SELECT id, col1, col2, ... FROM t0 WHERE id = ( SELECT max(id) FROM t0 WHERE col1 = value1 and col2 = value2 and ... ); SELECT * FROM temptable; SELECT col1 FROM t1 INNER JOIN temptable ON (t0_id = temptable.id); COMMIT; === But it is not so useful. In the case in original letter the best way is to use only two queries and pass id from the result of the first query as an argument to the second query. See the other letter[3] in the thread. Temporary tables are useful for keeping a lot of rows to prevent copying them between client and server. [1]http://www.postgresql.org/message-id/flat/56B8E6F9.9070600@xxxxxxxxx [2]http://www.postgresql.org/message-id/CAKOSWNkRB0kfWHcw9CvOcRmkS8HuzrPVFLr0kKcjuYn6juK5NA@xxxxxxxxxxxxxx [3]http://www.postgresql.org/message-id/CAKOSWN=wX9Myw4q9mpiqESyizU4tWrGivgbw+3Ef=5Q60viFSg@xxxxxxxxxxxxxx -- 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
