Exception using dollar-quoted string

[Fichtenelch]

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Fichtenelch" <hill...@inf.fu-berlin.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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Kris Jurka]

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

[Fichtenelch]

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

---------------------------(end of broadcast)---------------------------

[Kris Jurka]

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

---------------------------(end of broadcast)---------------------------