On 2/8/16, Johannes <jotpe@posteo.de> 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