我應該為最多返回一行的 INSERT/UPDATE/DELETE 編寫一個標量函式還是一組返回函式？

我想將某些INSERT, UPDATEorDELETE語句嵌入到函式中，以增強可重用性。這些函式的共同點是操作單行（如果存在）。

一個範例是以下函式：

create function my_table_delete(_identifier int) returns setof int as
$BODY$
begin
   return query
   delete from    my_table
   where          identifier = _identifier
   returning      identifier;
end
$BODY$
language plpgsql volatile strict;

此函式接受一個標識符，刪除相應的行並在刪除成功的情況下返回相同的標識符。

我很不確定是否應該將其實現為集合返回函式（就像我上面所做的那樣）或標量函式。

我偏愛 set-returning 函式的理由如下：

1. 在標量函式NULL的情況下，如果找不到行，我們將有一個返回值（而在 set-returning 函式的情況下，我們將有 0 行）。必須處理NULLs 是一種痛苦。
2. 我正在使用集合（SQL 是關於集合的），所以繼續這樣做是有意義的。
3. 另外，我更喜歡返回標識符（1 row：成功，0 rows：不成功）而不是boolean（true：成功，false：不成功），因為它允許以下構造：

with d(id) as
(
   select * from my_table_delete(1234)
)
insert into log_table(id, action)
select      d.id, 'DELETE'
from        d;

如果我要返回 a boolean，上面的語句可能會變得不那麼優雅。

我很感興趣這些論點是否有意義。我對將邏輯嵌入數據庫很陌生，所以我不確定我是否正確解決了這個問題。

你怎麼看？

在一個稍微相關的註釋中，我還想知道是否應該將函式定義如下：

create function my_table_delete(_identifier int)
   returns table (identifier int)
as ...

這為輸出行的列提供了名稱，使用起來更加方便。

首先，讓我們斷言操作單行UNIQUE是由 a orPRIMARY KEY約束 積極保證的my_table.identifier。否則它應該是一個表函式。

下一篇：你應該為這樣一個簡單的函式編寫一個函式DELETE嗎？還是用平原DELETE代替？幾乎沒有更長的時間和更少的成本。（也許你的實際功能更複雜……）

然後，雖然您的函式很簡單，但一個普通的 SQL 函式可能會提供：

CREATE FUNCTION my_table_delete(_identifier int)
 RETURNS TABLE(identifier int)
$func$
  DELETE FROM my_table t
  WHERE  t.identifier = _identifier
  RETURNING t.identifier;
$func$  LANGUAGE sql VOLATILE STRICT;

也RETURNS TABLE像你思考的那樣使用：注意表限定的列名以避免衝突。所有函式參數（包括RETURNS TABLE子句的列）在函式體中的 SQL 命令中都是可見的（在 PL/pgSQL 以及 SQL 函式中）。您需要避免列名和參數之間的衝突。

SQL 函式的成本較小。但是如果你在同一個會話中多次呼叫該函式，PL/pgSQL 變體實際上可能更快，因為它管理 SQL 語句就像準備語句一樣，這意味著如果滿足某些先決條件，查詢計劃將被保存和重用。再說一遍：使用準備好的語句可以達到許多相同的效果。看：

• 功能性能
• PostgreSQL 函式中語言 sql 和語言 plpgsql 的區別
• 選擇連結到另一列中的確切值集的列值

至於你的推理：

*1.*如果不想處理NULL，則案子了結。使它成為一個表函式。但你可能不得不處理NULL：各種查詢構造（必須）將“無行”轉換為 NULL。

*2.*聽起來像是一個論點，但只是一個邏輯謬誤。不負重。

3.如果是您個人喜好，則再次結案。不能與之爭辯。但是數據修改 CTE 也可以與標量函式一起使用：

WITH d(id) AS (
  SELECT my_table_delete(1234)
  )
INSERT INTO log_table(id, action)
SELECT  d.id, 'DELETE'
FROM    d
WHERE   d.id IS NOT NULL;

總而言之：使用RETURNS TABLE，但對於給定的情況幾乎不重要。

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