將觸發器、函式和過程從 Oracle 遷移到 PostgreSQL

我們執行一個使用 Oracle 作為其後端的基於 Java 的應用程序。雖然它們相當簡單並且不會過度使用 Oracle 特定的函式，但我們有很多用 pl/sql 編寫的觸發器、函式和過程。

現在我們也被迫使用 PostgreSQL 後端執行相同的應用程序，這意味著我們將執行我們的程式碼的兩個實例，一個連接到 Oracle，另一個連接到 PostgreSQL。我們知道我們將需要進行一些調整，但希望將它們保持在最低限度。

是否可以將我們的大部分觸發器、函式和過程轉移到 PostgreSQL 中？我看過遷移指南，聽說 PostgreSQL 只支持 C 作為編寫觸發器等的語言。

為了讓您了解我正在談論的複雜性：

CREATE OR REPLACE FUNCTION "STAGE"."NEST"
(X IN number, Y IN number, Z IN NUMBER)
RETURN number

AS

NL number;
BEGIN
 select count(*) into NL from ABC where foo_id = X
       and Y < A and Z > B order by Z;
 RETURN NL;
END;

還有一個觸發器：

CREATE OR REPLACE TRIGGER "STAGE"."SOMETHING" 
 BEFORE INSERT
 on  "CONFIG"
 for each row
begin
  SELECT CONF_ID_SEQ.NEXTVAL INTO :new.CONF_ID FROM DUAL;
END;

您當然可以用 C 以外的語言編寫觸發器、函式和儲存過程（自 Postgres 11 起）——最流行的可能是類似於 Oracle 的 PL/SQL 的 PL/pgSQL。

您還可以在 SQL 中編寫函式，這對於簡單的“查詢封裝”更有效。

我會將您問題中的函式實現為一個簡單的 SQL 函式：

CREATE OR REPLACE FUNCTION stage.nest(x integer, y integer, z integer)
RETURNS bigint
AS
$$
 select count(*) 
  from ABC 
  where foo_id = X
    and Y < A 
    and Z > B;
$$
language SQL;

我order by從該語句中刪除了，因為它對於返回 a 的查詢沒有用count(*)

使用 PL/pgSQL 實現的相同功能是：

CREATE OR REPLACE FUNCTION stage.nest(x integer, y integer, z integer)
RETURNS bigint
AS
$$
declare
 l_count bigint;
begin

 select count(*) 
   into l_count
 from ABC 
 where foo_id = X
   and Y < A 
   and Z > B;

 return l_count;
end;
$$
language SQL;

但一般來說，只有在確實需要過程邏輯（循環、IF 語句等）時才應使用 PL/pgSQL。否則language sql效率更高。

---

您真的不需要 Postgres 中的觸發器自動從序列中生成 ID 值。只需將列定義為serial，事情就會得到處理。如果要強制使用序列，請將列定義為integer generated always as identity

然而，觸發器在 Postgres 中將非常相似，最大的區別在於 Postgres 使用了在定義觸發器時引用的觸發器函式。

create function f_config_trg()
 returns trigger
as
$$
begin
 new.CONF_ID := nextval('confi_id_seq');
end;
$$
language plpgsql;

create trigger config_trigger
 before insert on config
 for each row
 execute procedure f_config_trg();

---

您可以在手冊中找到有關 PL/pgSQL、函式和触發器函式的更多詳細資訊

---

與您的問題無關：不要養成在 Postgres（或就此而言的 Oracle）中使用帶引號的標識符的習慣。從長遠來看，它們帶來的麻煩遠多於它們的價值。

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