一般来说,分解行从函数返回并获取各个列:
SELECT * FROM account_servicetier_for_day(20424, '2014-08-12');
至于查询:
Postgres 9.3 或更高版本
清洁剂与JOIN LATERAL
:
SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes
, a.username, a.accountid, a.userid
, f.* -- but avoid duplicate column names!
FROM account_tab a
, account_servicetier_for_day(a.accountid, '2014-08-12') f -- <-- HERE
WHERE a.isdsl = 1
AND a.dslservicetypeid IS NOT NULL
AND NOT EXISTS (
SELECT FROM dailyaccounting_tab
WHERE day = '2014-08-12'
AND accountid = a.accountid
)
ORDER BY a.username;
The LATERAL
关键字在这里是隐式的,函数总是可以更早地引用FROM
items. 手册:
LATERAL
也可以在函数调用之前FROM
项,但是在这个
如果是干扰词,因为函数表达式可以引用
早些时候FROM
任何情况下的物品。
Related:
中带逗号的短记号FROM
列表(大部分)相当于CROSS JOIN LATERAL
(与...一样[INNER] JOIN LATERAL ... ON TRUE
),从而从结果中删除函数调用不返回行的行。要保留此类行,请使用LEFT JOIN LATERAL ... ON TRUE
:
...
FROM account_tab a
LEFT JOIN LATERAL account_servicetier_for_day(a.accountid, '2014-08-12') f ON TRUE
...
另外,不要使用NOT IN (subquery)
当你可以避免它的时候。这是几种方法中最慢且最棘手的:
我建议NOT EXISTS
反而。
Postgres 9.2 或更高版本
您可以在SELECT
list(这是标准 SQL 的 Postgres 扩展)。出于性能原因,最好在子查询中完成此操作。分解外部查询中的(众所周知的!)行类型,以避免重复计算函数:
SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes
, a.username, a.accountid, a.userid
, (a.rec).* -- but be wary of duplicate column names!
FROM (
SELECT *, account_servicetier_for_day(a.accountid, '2014-08-12') AS rec
FROM account_tab a
WHERE a.isdsl = 1
AND a.dslservicetypeid Is Not Null
AND NOT EXISTS (
SELECT FROM dailyaccounting_tab
WHERE day = '2014-08-12'
AND accountid = a.accountid
)
) a
ORDER BY a.username;
Craig Ringer 的相关答案并解释了为什么最好不要在同一查询级别上分解:
- 如何在 SQL 查询中使用 (func()).* 语法避免多次函数评估?
Postgres 10已删除some集合返回函数的行为中的奇怪之处SELECT
:
- SELECT 子句中多个返回集合的函数的预期行为是什么?