Import PostgreSQL triggers on startup. Hibernate

Question

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?

Just adding: the syntax error is probably because of something like this http://stackoverflow.com/questions/4674828/how-to-break-expression-into-more-lines-in-hibernates-import-sql but even adding multiline parsing is not enough since the hibernate MultipleLinesSqlCommandExtractor will consider the function complete after the first semicolon, causing another syntax error. Adding the trigger should work this way, tho. — mabi, Feb 26 '13 at 15:09

score 1 · Accepted Answer · edited May 23 '17 at 12:27

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.

Thanks! It works! `@Singleton @Startup public class Init { @Inject private Logger log; private EntityManager em; @PersistenceContext(unitName = "EntityManager") public void setEm(EntityManager em) { this.em = em; } @PostConstruct public void postConstruct() { log.info("Create triggers"); Query q = em.createNativeQuery("CREATE TRIGGER logging_trigger " + "AFTER INSERT OR UPDATE OR DELETE ON some_entity " + " FOR EACH ROW EXECUTE PROCEDURE log_function();"); q.executeUpdate(); } } ` — Olga, Sep 28 '12 at 12:17

Import PostgreSQL triggers on startup. Hibernate

1 Answers1

Linked