Postgresql
返回帶有附加列的表的函式
我想編寫一個返回表的 PostgreSQL 函式,以及一個附加列。有沒有辦法在不手動指定的情況下做到這一點
RETURNS TABLE (col1 type, col2 type, ...)
?例如,考慮以下函式:
CREATE FUNCTION get_users_with_most_videos_since_time(ts TIMESTAMPTZ) RETURNS SETOF "user" AS $$ SELECT u.*, count(v.id) AS vids_since FROM "user" AS u INNER JOIN "video" AS v ON v.creator_id = u.id WHERE v.created_at > ts GROUP BY u.id ORDER BY vids_since DESC; $$ LANGUAGE SQL;
這失敗並出現錯誤:
ERROR: return type mismatch in function declared to return "user" DETAIL: Final statement returns too many columns.
很公平,我們包含了該表
vids_since
中不存在的"user"
列。因此,要解決此問題,我想將其更改為:
CREATE FUNCTION get_users_with_most_videos_since_time(ts TIMESTAMPTZ) RETURNS TABLE (<<< all columns from table "user" >>>, vids_since BIGINT) AS $$ ...
有沒有辦法做到這一點而不必在這裡重新復製
"user"
表的整個模式?
AIUI,你的願望是縮短
RETURNS
函式的子句。不確定是否要同時建立對錶的行類型的依賴關係,但這在這裡也有意義。該表單
RETURNS SETOF
*rettype
*依賴於儲存在系統目錄中的使用類型。手冊:返回類型可以是基類型、複合類型或域類型,也可以引用表列的類型。
因此,只需在系統中註冊您想要的行類型(一次)。您可以使用 顯式創建複合類型
CREATE TYPE
。或者,您可以通過創建另一個表、視圖或物化視圖來隱式執行此操作。甚至只是臨時功能的臨時視圖或表(在會話結束時死亡)。就像@Abelisto 評論的那樣:CREATE VIEW user_plus AS SELECT *, null::bigint AS vids_since FROM "user" WHERE false;
但是,在視圖創建時
SELECT *
解析為列列表(“早期綁定”)。如果稍後將列添加到基礎表,則視圖及其行類型不會更新,並且在執行函式時會出現另一種類型不匹配。但是,嘗試從基礎表中刪除列會抱怨視圖中依賴於它的列。而且您必須更改視圖和功能。順便說一句,您的功能可以簡化和更快:
CREATE FUNCTION get_users_with_most_videos_since_time(_ts timestamptz) RETURNS SETOF user_plus AS $func$ SELECT * -- effectively the same as: u.*, v.vids_since FROM "user" AS u JOIN ( -- aggregate *before* you join SELECT creator_id AS id, count(*) AS vids_since -- note the column alias FROM video v WHERE created_at > _ts GROUP BY 1 ) v USING (id) -- USING only retains one id column ORDER BY v.vids_since DESC; $func$ LANGUAGE SQL;
擁有這個功能實際上是有意義的。您想按不在結果中的列進行過濾。普通的
VIEW
.另外:我不鼓勵使用像user這樣的保留字作為標識符。那是一把上膛的足槍。