启动时导入PostgreSQL触发器。过冬 [英] Import PostgreSQL triggers on startup. Hibernate

问题描述

我在PostgreSQL中有一些存储函数和触发器。
例如：
$ b $ pre $ CREATE OR REPLACE FUNCTION log_function（）RETURNS TRIGGER AS $ logger $
BEGIN
IF（TG_OP ='DELETE'）THEN
INSERT INTO logger SELECT'D'，now（），user，OLD。*;
RETURN OLD;
END IF;
RETURN NULL;
END;
$ logger $语言plpgsql;


并触发：

  CREATE TRIGGER logging_trigger 
 AFTER INSERT或UPDATE或DELETE ON some_entity 
 FOR EACH ROW EXECUTE PROCEDURE log_function（）; 
  

我想在每次部署后导入这段代码。 （我有< property name =hibernate.hbm2ddl.autovalue =create-drop/> ）

我认为这是不可能的功能，因为它的plpgsql，但触发它会很好。
我一直在尝试向import.sql添加触发器，但是我得到了 org.postgresql.util.PSQLException：错误：输入结尾的语法错误

我不想将此代码移到Java级别。

有什么想法？

创建一个 @Startup @Singleton EJB ，当它的 @PostConstruct 方法被调用。你不能直接从SQL调用它，因为它是一个触发器过程，但你可以：

• INSERT ， UPDATE 或 DELETE some_entity 使触发器触发;或更明智地定义一个名为类似于 log_startup 的单独过程，该过程返回 void ，所以它可以从SQL调用为 SELECT log_startup（），并且具有这些：
  
  

    INSERT INTO记录器SELECT'S'，now（），user，NULL; 
    

  或其他。
  

您可以使用本机查询（如 SELECT log_startup（））调用 log_startup 应该也可以使用JDBC或HQL存储过程调用语法 {call log_startup（）} （未测试）。

您无法直接调用触发器过程，因此无法 在展开时运行确切的函数。

I have some stored function and triggers in my PostgreSQL. E.g. :

CREATE OR REPLACE FUNCTION log_function() RETURNS TRIGGER AS $logger$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO logger SELECT 'D', now(), user, OLD.*;
        RETURN OLD;
    END IF;
    RETURN NULL; 
END;
$logger$ LANGUAGE plpgsql;

And trigger:

CREATE TRIGGER logging_trigger
AFTER INSERT OR UPDATE OR DELETE ON some_entity
    FOR EACH ROW EXECUTE PROCEDURE log_function();

And I would like import this code after each deploy. (I have <property name="hibernate.hbm2ddl.auto" value="create-drop"/>)

I think it is impossible for functions because its plpgsql, but for triggers it would be great. I have been trying added triggers to import.sql, but I have got org.postgresql.util.PSQLException: ERROR: syntax error at end of input

And I don't want to move this code to Java level.

Any ideas?

解决方案

Create an @Startup @Singleton EJB, and have that invoke the desired procedure when its @PostConstruct method is invoked. You cannot invoke it directly from SQL because it's a trigger procedure, however you could:

• INSERT, UPDATE or DELETE a row in some_entity to cause the trigger to fire; or more sensibly

• Define a separate procedure named something like log_startup that returns void so it can be called from SQL as SELECT log_startup(), and have that:

    INSERT INTO logger SELECT 'S', now(), user, NULL;
  

  or whatever.

You can call log_startup using a native query like SELECT log_startup() and it should also be possible to use JDBC or HQL stored procedure call syntax {call log_startup()} (untested).

You simply cannot call a trigger procedure directly, so there is no way to have that exact function run on deploy.

这篇关于启动时导入PostgreSQL触发器。过冬的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！