将触发器、函数和过程从Oracle迁移到PostgreSQL

Question

我们运行一个基于Java的应用程序，它使用Oracle作为后端。虽然它们相当简单，并且没有过度使用Oracle特定的函数，但是我们有很多用pl/sql编写的触发器、函数和过程。

现在，我们也被迫使用PostgreSQL后端运行同一个应用程序，这意味着我们将运行两个代码实例，一个连接到PostgreSQL，另一个连接到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;

Answer 1

当然，您可以用C以外的语言编写触发器、函数和存储过程(因为Postgres 11) --最流行的可能是PL/pgSQL，它类似于Oracle的PL/SQL。

您还可以用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，因为它对返回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;

但是一般来说，只有当您需要过程逻辑(循环、如果语句等)时，才应该使用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 )中使用引号标识符的习惯。从长远来看，他们的麻烦远远超过了他们的价值。