In PostgreSQL, how do I use table values in VALUES?-CodePudding

I want to implement something like this.

WITH
t1 AS (
    SELECT a, b, c, d, e, f
    FROM tbl
    WHERE a = 1
    LIMIT 1
),
t2 (g, h, i) AS (
    SELECT b, c, d
    FROM t1
),
t3 (j, k, l) AS (
    VALUES (t2.g,     t2.h,      t2.i),
           (t2.g   1, t2.h - 10, t2.i   10),
           (t2.g   1, t2.h - 20, t2.i   20)
)
SELECT j, k, l
FROM t3

Everything is working, except using t2 in VALUES

CodePudding user response：

Use UNION ALL:

...,
t3 (j, k, l) AS (
    SELECT t2.g,     t2.h,      t2.i      FROM t2
    UNION ALL
    SELECT t2.g   1, t2.h - 10, t2.i   10 FROM t2
    UNION ALL
    SELECT t2.g   1, t2.h - 20, t2.i   20 FROM t2
)