I am unable to create functions via postgres jdbc. It seems that the driver strips out newlines and semi-colons, resulting in syntax errors rejected by the db. I'm trying to setup dbdeploy for the project I'm working on. All db artifacts (tables, sequence, views) are successfully managed except for triggers.
ERROR: syntax error at or near "$" at character 89
STATEMENT: CREATE FUNCTION trigger_update_dates() RETURNS trigger LANGUAGE plpgsql AS $ BEGIN IF TG_OP = 'INSERT' THEN NEW.CREATE_DATE := LOCALTIMESTAMP
Stacktrace:
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$"
Position: 89
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)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:395)
at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:773)
at org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:743)
at org.apache.tools.ant.taskdefs.SQLExec$Transaction.runTransaction(SQLExec.java:1053)
at org.apache.tools.ant.taskdefs.SQLExec$Transaction.access$000(SQLExec.java:983)
at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:651)
Trigger:
CREATE FUNCTION trigger_update_dates() RETURNS trigger