Dollar-Quoted Postgres pl/pgsql procedures abort at first semi-colon

I'm trying to create pl/pgsql stored procedures in Postgres.

They are typically of the format:

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

(This example is from http://www.postgresql.org/docs/8.2/interactive/plpgsql-declarations.html)

The dollar-quotes ($$) avoid the need to double-quote enclosed text, a bit like Javascript allows the use of single quotes within double-quotes.

However, when DbVisualizer sends this SQL to Postgres, it returns the error:

13:19:14  [CREATE - 0 row(s), 0.000 secs]  [Error Code: 0, SQL State: 42601]  ERROR: unterminated dollar-quoted string at or near "$$
    BEGIN
        RETURN subtotal * 0.06"
13:19:14  [END - 0 row(s), 0.000 secs]  Command processed . No rows were affected

From what I can observe, the code is being terminated at the semi-colon after '0.06', rather than being interpreted as an entire block. The example runs fine if the dollar-quotes ($$) are replaced with single quotes (').

I have tried changing the Statement Delimiters (no luck) and the Variable Identifers (away from $$) but the above error still happens.

If I execute the same code within another query tool (eg Query Tool for Postgres), it executes fine. Thus, it is something to do with DbVisualizer's interaction with Postgres.

Oh, and yes -- I used the button labelled "Execute the complete buffer as one SQL statement".

Any suggestions?

Thanks!

Hi John, We recently ran into this problem as well. The main problem is that the PostgreSQL JDBC driver did not handle dollar-quoted strings until a version later than 8.1 (not sure exactly when it was fixed). Another problem is that "$$" is used as a variable delimiter within DbVis, but as you have already seen, you can change that to something else, e.g., "$$$" instead of "$$". If you upgrade your JDBC driver (I've tested with PostgreSQL 8.2 JDBC3 with SSL, build 506) set the Variable Delimiter to something other than "$$", and use Execute Buffer (or enclose the statement with block delimiters, "--/" and "/" by default), you should be fine. Regards, Hans

Thank you Hans! I can confirm that updating to a newer postgres driver (I used postgresql-8.2-506.jdbc3.jar) has fixed this problem. (I also had an additional stray driver in my java classpath that I had to remove to get this new one recognised.) Thank you! John.

hi, i am facing this issue when I execute functions/procedures from ant so please help me. Srinivas

Hello, I have the exact same problem but the solutions provided don't seem to work for me. I use a current version of DbVisualizer 9.08. which provides a PostgreSQL JDBC driver version of 9.1 and I double checked the variable settings, there are on ${, }$ and ||, no $$ anywhere. In the debug log window I can clearly see that my CREATE FUNCTION statement gets truncated on the first ;: 12:23:51 [DEBUG pool-3-thread-2 B.?] DefaultEditor14: Jdbc4Statement.execute("CREATE FUNCTION find_deletable_objects() RETURNS void AS $$ DECLARE objectId integer") org.postgresql.util.PSQLException: FEHLER: Dollar-Quotes nicht abgeschlossen bei »$$ DECLARE objectId integer« Position: 58 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:372) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:364) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.B.?(Z:1910) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) SQLException: SQLState(42601) The only thing which seems to work is surrounding the complete CREATE FUNCTION statement with --/ and /. Is this intended behavior or am I doing somethign wrong? Thanks!