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

You can have a look here

http://stackoverflow.com/questions/10460542/create-postgresql-trigger-using-jdbc

I suspect the parser is trying to parse the $$ but it appears you aren't
the first to try to solve this problem

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 16 June 2014 00:41, Coffee GoesToSchool <coffeegoestoschool(at)gmail(dot)com>
wrote:

>
> 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
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>