Thread: 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.

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@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.

Since this appears to be an ANT task and $ has a special meaning for it,
have you tried setting "expandproperties" to "false"?
http://ant.apache.org/manual/Tasks/sql.html

Maybe there is also some other way to tell ANT to treat that $$ signs as
verbatim (perhaps
http://stackoverflow.com/questions/2631082/escaping-a-dollar-in-the-middle-of-an-ant-property
).


-hannes