Exception using dollar-quoted string

Hi,

when I'm trying to execute the following command with a java.sql.Statement
an error occurs:

CREATE OR REPLACE FUNCTION test(text) RETURNS void AS $$ DECLARE tmp text;
BEGIN tmp := 'test'; END; $$ LANGUAGE plpgsql;

org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string
at or near "$$ DECLARE tmp text"
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:1512)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1297)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:437)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:339)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:331)
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
org.postgresql.ds.common.PooledConnectionImpl$StatementHandler.invoke(Pooled
ConnectionImpl.java:467)
at $Proxy2.execute(Unknown Source)

The command: CREATE OR REPLACE FUNCTION test(text) RETURNS void AS ' DECLARE
tmp text; BEGIN tmp := "test"; END; ' LANGUAGE plpgsql;

works fine, also creating the function with php (phppgadmin). I have the
latest version of the jdbc driver.
Is this a bug, or my fault?

Thanks,
Fichtenelch

"Fichtenelch" <hillebra(at)inf(dot)fu-berlin(dot)de> writes:
> when I'm trying to execute the following command with a java.sql.Statement
> an error occurs:

> CREATE OR REPLACE FUNCTION test(text) RETURNS void AS $$ DECLARE tmp text;
> BEGIN tmp := 'test'; END; $$ LANGUAGE plpgsql;

I'm pretty sure the jdbc driver doesn't understand dollar-quoting yet.
(Patches to fix this would be gratefully accepted no doubt...)

regards, tom lane

On Fri, 24 Mar 2006, Fichtenelch wrote:

> works fine, also creating the function with php (phppgadmin). I have the
> latest version of the jdbc driver.
> Is this a bug, or my fault?
>

This is a known bug in the JDBC driver. It does not support dollar
quotes.

Kris Jurka

And how are sql commands like

INSERT INTO table VALUES ($t$some text$t$)

(or UPDATE ...)

passed to the database? In that case dollar quotes are working
definitely.

Fichtenelch

> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-
> owner(at)postgresql(dot)org] On Behalf Of Kris Jurka
> Sent: Friday, March 24, 2006 7:29 PM
> To: Fichtenelch
> Cc: pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] Exception using dollar-quoted string
>
>
>
> On Fri, 24 Mar 2006, Fichtenelch wrote:
>
> > works fine, also creating the function with php (phppgadmin). I have the
> > latest version of the jdbc driver.
> > Is this a bug, or my fault?
> >
>
> This is a known bug in the JDBC driver. It does not support dollar
> quotes.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

On Fri, 24 Mar 2006, Fichtenelch wrote:

> And how are sql commands like
>
> INSERT INTO table VALUES ($t$some text$t$)
>
> passed to the database? In that case dollar quotes are working
> definitely.
>

The JDBC driver executes multiple queries within the same string
(Statement.execute("SELECT 1; SELECT 2")) by splitting them on the
semicolon and executing them separately. So the difference is that your
plpgsql function body has semicolons while your simple example does not.

Kris Jurka