嘗試在另一個查詢中使用集合回傳函數執行查詢時會出現此問題,導致所有函數傳回列被連接成一列。
以下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中文網其他相關文章!