PostgreSQL 触发器和临时表

Question

我在 postgresql 数据库表上创建了更新前和更新后触发器。

要求保留历史记录，同时为该数据创建新的记录。旧记录将被标记为已存档。

我计划使用临时表来跟踪新值并重置新值，以便将其标记为已存档。

在我的更新后触发器中，我将从临时表中读取数据，并创建一个全新的事件记录。

我的问题是更新触发器之前创建的临时表对更新触发器不可见。此外，我什至不能将任何参数(记录类型)传递给更新后触发器，因为这是不允许的。

我已经在 Oracle 数据库中使用全局临时表实现了预期的结果，但在 PostgreSQL 中遇到了困难。

更新前触发函数的示例代码如下:

CREATE OR REPLACE FUNCTION trigger_fct_trig_trk_beforeupdate()
RETURNS trigger AS
$BODY$
DECLARE

    some variable declarations;
    BEGIN

    Drop table  IF EXISTS track_tmp_test;

    CREATE TEMPORARY TABLE track_tmp_test(
    ...
    );

    Insert into track_tmp_test (........)
    values(NEW., NEW..., NEW.., NEW...);

    NEW... := OLD...;
    NEW... := OLD.... ;
    NEW... := OLD...;
    Mark the NEW.status : = 'archived';

RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER trig_trk_test_beforeupdate
BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE trigger_fct_trig_trk_beforeupdate() ;

现在更新后触发函数:

CREATE OR REPLACE FUNCTION trigger_fct_trg_trk_afterupdate()
  RETURNS trigger AS
$BODY$
DECLARE

    some variables;

-- insert into  original table the data from temporary that was inserted in before update trigger 
    INSERT into TEST (....)
    select ....
    from track_tmp_test ;

    -- delete data from temporary table after insert
    delete from track_tmp_test ;

 EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

有没有办法让after update trigger可以访问在before update trigger函数中创建的临时表？

我不能有一个永久表来保存他的值，因为许多用户更新表中的数据可能会触发触发器。

Answer 1

从触发器访问临时表没有问题，并且以下代码可以正常工作(在 PostgreSQL 9.4 上):

CREATE OR REPLACE FUNCTION public.f1()
RETURNS trigger
LANGUAGE plpgsql
AS $function$ 
begin
  drop table if exists bubu; 
  create temp table bubu(a int); 
  insert into bubu values(10); 
  return new; 
end
$function$

CREATE OR REPLACE FUNCTION public.f2()
RETURNS trigger
LANGUAGE plpgsql
AS $function$ 
declare r record;
begin 
  for r in select * from bubu 
  loop 
    raise notice '%', r; 
  end loop; 
  return null; 
end 
$function$

create trigger xx
  before insert on omega
  for each row execute procedure f1();

create trigger yy
  after insert on omega
  for each row execute procedure f2();

postgres=# insert into omega values(333);
NOTICE:  (10)
INSERT 0 1

所以我确定，您的问题不会出在访问临时表上。它运作良好。某些 8.2、8.3 和更早版本可能会出现问题，因为对丢弃对象的引用无效计划。这不是你的问题吗？

我可以说，所以你的设计是错误的 - 没有任何理由，为什么你必须使用临时表。你可以在触发后做同样的工作。触发器内的任何操作都应该很快，非常快。删除或创建临时表不是快速操作。

如果您有较旧的 PostgreSQL 版本，则不必每次都删除临时表。您应该只删除内容。看文章http://postgres.cz/wiki/Automatic_execution_plan_caching_in_PL/pgSQL