使用CTE对from子句表项的无效引用

Question

我正试图编写一个查询来生成时间段，但我得到错误:

ERROR: invalid reference to FROM-clause entry for table "prop"
Hint: There is an entry for table "prop", but it cannot be referenced from this part of the query.
Position: 346

查询为:

WITH prop AS (SELECT p.stagger, p.tariff FROM core_property p WHERE p.id = 1)
SELECT day + (hour.a * INTERVAL '1 hour') AS time,
day + (hour.b * INTERVAL '1 hour') AS timeEnd
FROM prop,
GENERATE_SERIES(date '2022-05-18' + prop.stagger, date '2022-05-20', '1 day'::INTERVAL) day,
(SELECT UNNEST(CASE
WHEN prop.tariff = 'Economy 7' THEN ARRAY [0,12,18]
WHEN prop.tariff = 'Economy 10' THEN ARRAY [0] END) a,
UNNEST(CASE
WHEN prop.tariff = 'Economy 7' THEN ARRAY [5,13,20]
WHEN prop.tariff = 'Economy 10' THEN ARRAY [7] END) b) hour;

我有一个版本的这个查询，几乎工作，但产生重复，是丑陋的:

SELECT day + p.stagger + (CASE
WHEN p.tariff = 'Economy 7' THEN e7.a
WHEN p.tariff = 'Economy 10' THEN e10.a END) * INTERVAL '1 hour' AS time,
day + p.stagger + (CASE
WHEN p.tariff = 'Economy 7' THEN e7.b
WHEN p.tariff = 'Economy 10' THEN e10.b END) * INTERVAL '1 hour' AS timeEnd
FROM GENERATE_SERIES(date '2022-05-18', date '2022-05-20', '1 day'::INTERVAL) day,
(SELECT UNNEST(ARRAY [0,13,20]) a, UNNEST(ARRAY [5,16,22]) b) e10,
(SELECT UNNEST(ARRAY [0]) a, UNNEST(ARRAY [7]) b) e7,
(SELECT p.stagger, p.tariff FROM core_property p WHERE p.id = 1) p;

当p.tariff = 'Economy 7'时，结果是这样的，但是每天应该只有一个条目:

+---------------------------------+---------------------------------+
|time                             |timeend                          |
+---------------------------------+---------------------------------+
|2022-05-18 00:15:00.000000 +00:00|2022-05-18 07:15:00.000000 +00:00|
|2022-05-18 00:15:00.000000 +00:00|2022-05-18 07:15:00.000000 +00:00|
|2022-05-18 00:15:00.000000 +00:00|2022-05-18 07:15:00.000000 +00:00|
|2022-05-19 00:15:00.000000 +00:00|2022-05-19 07:15:00.000000 +00:00|
|2022-05-19 00:15:00.000000 +00:00|2022-05-19 07:15:00.000000 +00:00|
|2022-05-19 00:15:00.000000 +00:00|2022-05-19 07:15:00.000000 +00:00|
|2022-05-20 00:15:00.000000 +00:00|2022-05-20 07:15:00.000000 +00:00|
|2022-05-20 00:15:00.000000 +00:00|2022-05-20 07:15:00.000000 +00:00|
|2022-05-20 00:15:00.000000 +00:00|2022-05-20 07:15:00.000000 +00:00|
+---------------------------------+---------------------------------+

当p.tariff = 'Economy 10'时，那么结果是正确的:

+---------------------------------+---------------------------------+
|time                             |timeend                          |
+---------------------------------+---------------------------------+
|2022-05-18 00:15:00.000000 +00:00|2022-05-18 05:15:00.000000 +00:00|
|2022-05-18 13:15:00.000000 +00:00|2022-05-18 16:15:00.000000 +00:00|
|2022-05-18 20:15:00.000000 +00:00|2022-05-18 22:15:00.000000 +00:00|
|2022-05-19 00:15:00.000000 +00:00|2022-05-19 05:15:00.000000 +00:00|
|2022-05-19 13:15:00.000000 +00:00|2022-05-19 16:15:00.000000 +00:00|
|2022-05-19 20:15:00.000000 +00:00|2022-05-19 22:15:00.000000 +00:00|
|2022-05-20 00:15:00.000000 +00:00|2022-05-20 05:15:00.000000 +00:00|
|2022-05-20 13:15:00.000000 +00:00|2022-05-20 16:15:00.000000 +00:00|
|2022-05-20 20:15:00.000000 +00:00|2022-05-20 22:15:00.000000 +00:00|
+---------------------------------+---------------------------------+

我不确定我是否真的需要CTE，但这似乎是使用prop的唯一方法。关税和道具。

Answer 1

FROM/JOIN列表中的子查询不能引用FROM/JOIN列表中的其他表，除非将该连接声明为lateral。所以声明为LATERAL。

WITH prop AS (SELECT p.stagger, p.tariff FROM core_property p WHERE p.id = 1)
SELECT day + (hour.a * INTERVAL '1 hour') AS time,
day + (hour.b * INTERVAL '1 hour') AS timeEnd
FROM prop,
GENERATE_SERIES(date '2022-05-18' + prop.stagger, date '2022-05-20', '1 day'::INTERVAL) day, 
LATERAL (SELECT UNNEST(CASE
WHEN prop.tariff = 'Economy 7' THEN ARRAY [0,12,18]
WHEN prop.tariff = 'Economy 10' THEN ARRAY [0] END) a,
UNNEST(CASE
WHEN prop.tariff = 'Economy 7' THEN ARRAY [5,13,20]
WHEN prop.tariff = 'Economy 10' THEN ARRAY [7] END) b) hour;

一个例外是在FROM/JOIN列表中使用的裸集返回函数，它被隐式地视为横向的。这就是为什么' generate_series(…)不会抛出相同的错误。