Start a new topic

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

[This topic is migrated from our old forums. The original author name has been removed]

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!


[This reply is migrated from our old forums.]

Re: Dollar-Quoted Postgres pl/pgsql procedures abort at first semi-colon
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
[This reply is migrated from our old forums. The original author name has been removed]

Re: Dollar-Quoted Postgres pl/pgsql procedures abort at first semi-colon
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.
[This reply is migrated from our old forums. The original author name has been removed]

Re: Dollar-Quoted Postgres pl/pgsql procedures abort at first semi-colon
hi, i am facing this issue when I execute functions/procedures from ant so please help me. Srinivas
[This reply is migrated from our old forums.]

Re: Dollar-Quoted Postgres pl/pgsql procedures abort at first semi-colon
Hi, You say: "... when I execute functions/procedures from ant" How does this relate with DbVisualizer? Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Dollar-Quoted Postgres pl/pgsql procedures abort at first semi-colon
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!
[This reply is migrated from our old forums.]

Re: Dollar-Quoted Postgres pl/pgsql procedures abort at first semi-colon
Thorsten, This is the general recommendation when dealing with application objects such as functions: 1) If the only editor contains only the SQL block that should be executed, then use SQL->Execute Buffer 2) Enclose the complete block as an SQL block i.e. between "--/" and "/": --/ CREATE ... / 3) Use the @delimiter client side command to temporarily re-define the statement delimiter: @delimiter ++; CREATE ... @delimiter ;++ I hope this helps. Regards Roger

Overdue update: This issue was fixed in version 12 and is described in the section Using an SQL Dialect in the Users Guide.