Unable to create trigger via dbdeploy for postgres db

Question

I get the following error when trying to create a trigger on postgres via dbdeploy. The issue seems to be a postgres jdbc issue rather than dbdeploy. The sql statement seems to be ignored after the first semi-colon in the sql block. Any help with getting past this problem is appreciated.

The sql string (which works perfectly via pgAdmin):

    CREATE FUNCTION trigger_history() RETURNS trigger
        LANGUAGE plpgsql
        AS $$
    BEGIN
        INSERT INTO table_history(col_one, col_two)
        VALUES(NEW.col_one, OLD.col_two);
        RETURN NEW;
    END
    $$;

The error:

Caused by: org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string at or near "$ BEGIN INSERT INTO table_history(col_one, col_two) VALUES(NEW.col_one, OLD.col_two)"
  Position: 97
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

Endrju · Accepted Answer · 2014-06-16 16:08:39Z

0

I think it's the semicolon causing the problem: $$; just delete it, or move it to the next line. The $$ needs to be at the end of the line.

answered Jun 16, 2014 at 16:08

Endrju

2,47619 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to create trigger via dbdeploy for postgres db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related