On 2/9/16, Marc Mamin <M.Mamin@intershop.de> 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@posteo.de
[2]http://www.postgresql.org/message-id/CAKOSWNkRB0kfWHcw9CvOcRmkS8HuzrPVFLr0kKcjuYn6juK5NA@mail.gmail.com
[3]http://www.postgresql.org/message-id/CAKOSWN=wX9Myw4q9mpiqESyizU4tWrGivgbw+3Ef=5Q60viFSg@mail.gmail.com
--
Best regards,
Vitaly Burovoy
