从函数返回的记录具有串联的列 [英] Record returned from function has columns concatenated
问题描述
我有一个表,用于存储帐户随时间的变化.如果这些记录尚不存在,我需要将其与另外两个表结合起来以创建特定日期的一些记录.
I have a table which stores account changes over time. I need to join that up with two other tables to create some records for a particular day, if those records don't already exist.
为使事情变得容易(我希望),我将返回正确历史数据的查询封装到一个接受帐户ID和日期的函数中.
To make things easier (I hope), I've encapsulated the query that returns the correct historical data into a function that takes in an account id, and the day.
如果执行"Select * account_servicetier_for_day(20424, '2014-08-12')"
,则会得到预期的结果(该函数返回的所有数据都在单独的列中).如果在另一个查询中使用该函数,则会将所有列合并为一个:
If I execute "Select * account_servicetier_for_day(20424, '2014-08-12')"
, I get the expected result (all the data returned from the function in separate columns). If I use the function within another query, I get all the columns joined into one:
("2014-08-12 14:20:37",hollenbeck,691,12129,20424,69.95,"2Mb/1Mb 20GB Limit",2048,1024,20.000)
我正在使用"x86_64-slackware-linux-gnu上的PostgreSQL 9.2.4,由gcc(GCC)4.7.1,64位编译".
I'm using "PostgreSQL 9.2.4 on x86_64-slackware-linux-gnu, compiled by gcc (GCC) 4.7.1, 64-bit".
查询:
Select
'2014-08-12' As day, 0 As inbytes, 0 As outbytes, acct.username, acct.accountid, acct.userid,
account_servicetier_for_day(acct.accountid, '2014-08-12')
From account_tab acct
Where acct.isdsl = 1
And acct.dslservicetypeid Is Not Null
And acct.accountid Not In (Select accountid From dailyaccounting_tab Where Day = '2014-08-12')
Order By acct.username
功能:
CREATE OR REPLACE FUNCTION account_servicetier_for_day(_accountid integer, _day timestamp without time zone) RETURNS setof account_dsl_history_info AS
$BODY$
DECLARE _accountingrow record;
BEGIN
Return Query
Select * From account_dsl_history_info
Where accountid = _accountid And timestamp <= _day + interval '1 day - 1 millisecond'
Order By timestamp Desc
Limit 1;
END;
$BODY$ LANGUAGE plpgsql;
推荐答案
通常,分解从函数返回的行并获取单个列:
SELECT * FROM account_servicetier_for_day(20424, '2014-08-12')
关于查询:
具有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 1
FROM dailyaccounting_tab
WHERE day = '2014-08-12'
AND accountid = a.accountid
)
ORDER BY a.username;
LATERAL
关键字在这里是隐式的,函数始终可以引用更早的FROM
项. 手册:
The LATERAL
keyword is implicit here, functions can always refer earlier FROM
items. The manual:
LATERAL
也可以在函数调用FROM
项之前,但是在此 情况下它是一个干扰词,因为函数表达式可以引用 在任何情况下都是较早的FROM项目.
LATERAL
can also precede a function-callFROM
item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.
相关:
在FROM
列表中带有逗号的缩写表示法(通常)等效于CROSS JOIN LATERAL
(与[INNER] JOIN LATERAL ... ON TRUE
相同),因此从结果中删除了行,其中函数调用不返回任何行.要保留此类行,请使用 LEFT JOIN LATERAL ... ON TRUE
:
The short notations with a comma in the FROM
list is (mostly) equivalent to a CROSS JOIN LATERAL
(same as [INNER] JOIN LATERAL ... ON TRUE
) and thus removes rows from the result where the function call returns no row. To retain such rows, use 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)
.这是几种方法中最慢,最棘手的:
Also, don't use NOT IN (subquery)
when you can avoid it. It's the slowest and most tricky of several ways to do that:
我建议使用NOT EXISTS
.
您可以在SELECT
列表中调用返回集合的函数(这是标准SQL的Postgres扩展).出于性能原因,最好在子查询中完成此操作.在外部查询中分解(众所周知的!)行类型,以避免重复评估该函数:
You can call a set-returning function in the SELECT
list (which is a Postgres extension of standard SQL). For performance reasons, this is best done in a subquery. Decompose the (well-known!) row type in the outer query to avoid repeated evaluation of the function:
SELECT '2014-08-12' AS day, 0 AS inbytes, 0 AS outbytes
, a.username, a.accountid, a.userid
, (a.rec).* -- but avoid 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 1
FROM dailyaccounting_tab
WHERE day = '2014-08-12'
AND accountid = a.accountid
)
) a
ORDER BY a.username;
克雷格·林格(Craig Ringer)给出的相关答案以及解释,为什么我们最好在外部查询中分解:
Related answer by Craig Ringer with an explanation, why we better decompose in the outer query: