尝试在另一个查询中使用集合返回函数执行查询时会出现此问题,导致所有函数返回列被连接成一列。
以下 Postgres 函数 account_servicetier_for_day 接受帐户 ID 和日期并返回历史数据:
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 '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
要将函数返回的记录分解为各个列,请使用 SQL 语法:
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;
对于 Postgres 9.2 或更早版本,使用子查询调用 set-returning 函数并分解外部查询中的记录:
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;
以上是为什么我的集合返回函数的列在 PostgreSQL 查询中使用时会串联起来,如何解决这个问题?的详细内容。更多信息请关注PHP中文网其他相关文章!