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

If the problem is with the $ quoting, you can use plain quoting. After
all, the main reason for using $ quoting is to avoid the need to escape
all the quotation marks within the function body. And JDBC can do that
for you if you use a prepared statement.

I would try using a "full" $ quoting first, that is, instead of $$ use
$func$ or something like that. But if that fails, use a prepared
statement just with

CREATE FUNCTION trigger_update_dates() RETURNS trigger LANGUAGE plpgsql AS ?

And then use setString() to add the body of the function, and execute()
the statement.

בתאריך 16/06/14, 16:15, ציטוט Dave Cramer:
> 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 <mailto: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
> <mailto:pgsql-jdbc(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>