返回帶有附加列的表的函式

我想編寫一個返回表的 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這樣的保留字作為標識符。那是一把上膛的足槍。

引用自：https://dba.stackexchange.com/questions/144600