Unable to create triggers via jdbc. Tried so many anythings. Please help.

From: Coffee GoesToSchool <coffeegoestoschool(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Unable to create triggers via jdbc. Tried so many anythings. Please help.
Date: 2014-06-16 04:41:57
Message-ID: CAFcAjPRpFc44Obp9RHK36f==APK9Rt95kkM71oc5tgoXR+oraw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi

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.

db version: postgres (PostgreSQL) 9.3.4
jdbc version: org.postgresql:postgresql:9.3-1100-jdbc41
platform: OS X 10.9.3

*Error in postgres log:*
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
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
NEW.CREATE_DATE := LOCALTIMESTAMP;
NEW.UPDATE_DATE := LOCALTIMESTAMP;
END IF;
IF TG_OP = 'UPDATE' THEN
NEW.UPDATE_DATE := LOCALTIMESTAMP;
END IF;
RETURN NEW;
END
$$;

---CoffeeGoesToSchool

Attachment Content-Type Size
stacktrace.txt text/plain 7.1 KB

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2014-06-16 13:15:03 Re: Unable to create triggers via jdbc. Tried so many anythings. Please help.
Previous Message amithbm 2014-06-13 09:01:20 Re: Hibernate over postgresql