Problem with dollar-quoted CREATE OR REPLACE FUNCTION

[Tim Penhey]

Hi All,

I am currently using the beta 2 dev 3 binary for 8.0 on WinXP.
I am using the pgdev.305.jdbc3.jar and eclipse for Java dev.

I have a CREATE OR REPLACE FUNCTION that uses $BODY$ to start and finish
the body of the function.
When submitting that through jdbc it complains:

ERROR: unterminated dollar-quoted string at or near "$BODY$ LANGUAGE
'plpgsql'"

If I copy and paste that create function statement and paste it in
pgAdmin, it works fine.

Any ideas?

Tim

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org

[Dave Cramer]

Can you show us the code, logs?

Dave

--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tim Penhey]

Dave Cramer wrote:

>Can you show us the code, logs?
>
>Dave
>
>

CREATE OR REPLACE FUNCTION quote_test(first INTEGER, second INTEGER)
RETURNS INTEGER AS $BODY$
DECLARE
result INTEGER;
BEGIN
result := first + second;
RETURN result;
END;
$BODY$
LANGUAGE 'plpgsql';

This gives the error in eclipse. Didn't mention before but using
MyEclipseIDE plugin for DB access in Eclipse.

Also, just noticed the jdbc directory with the 8.0 beta. Should I be
using that instead of the pgdev one?
It has pg74.214.jdbc1.jar, pg74.214.jdbc2.jar, pg74.214.jdbc2ee.jar,
pg74.214.jdbc3.jar. Would these be better?

Tim

---------------------------(end of broadcast)---------------------------
TIP 3: 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

[Dave Cramer]

Tim,

Ideally you should look in the logs, I suspect that if you escaped the $
with \$ it should work.

Dave

On Tue, 2004-09-14 at 10:55, Tim Penhey wrote:
> Dave Cramer wrote:
>
> >Can you show us the code, logs?
> >
> >Dave
> >
> >
>
> CREATE OR REPLACE FUNCTION quote_test(first INTEGER, second INTEGER)
> RETURNS INTEGER AS $BODY$
> DECLARE
> result INTEGER;
> BEGIN
> result := first + second;
> RETURN result;
> END;
> $BODY$
> LANGUAGE 'plpgsql';
>
>
> This gives the error in eclipse. Didn't mention before but using
> MyEclipseIDE plugin for DB access in Eclipse.
>
> Also, just noticed the jdbc directory with the 8.0 beta. Should I be
> using that instead of the pgdev one?
> It has pg74.214.jdbc1.jar, pg74.214.jdbc2.jar, pg74.214.jdbc2ee.jar,
> pg74.214.jdbc3.jar. Would these be better?
>
> Tim
>

[Dave Cramer]

What kind of machine is it ?

/var/log/postgres.log ?

Dave

On Tue, 2004-09-14 at 13:26, Tim Penhey wrote:
> Dave Cramer wrote:
>
> >Tim,
> >
> >Ideally you should look in the logs, I suspect that if you escaped the $
> >with \$ it should work.
> >
> >Dave
> >
> >

> Nope, the only thing that \$ did was give me the error:
>
> ERROR: syntax error at or near "\"
>
> Where would I find the logs?

>
> Tim
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com

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

[Tim Penhey]

Dave Cramer wrote:

>What kind of machine is it ?
>
>/var/log/postgres.log ?
>
>

Windows XP.

Checked the event log, but nothing of any consequence there.

Tim

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Kris Jurka]

On Tue, 14 Sep 2004, Tim Penhey wrote:

> Hi All,
>
> I am currently using the beta 2 dev 3 binary for 8.0 on WinXP.
> I am using the pgdev.305.jdbc3.jar and eclipse for Java dev.
>
> I have a CREATE OR REPLACE FUNCTION that uses $BODY$ to start and finish
> the body of the function.
> When submitting that through jdbc it complains:
>
> ERROR: unterminated dollar-quoted string at or near "$BODY$ LANGUAGE
> 'plpgsql'"
>

The problem is that the jdbc driver does not have any knowledge of dollar
quoting. It is trying to split your statement on semi colons and send
each piece separately. That is something like "SELECT 1; SELECT 2" would
be sent as two individual queries by the driver. The driver needs to do
the splitting to use the V3 extended query protocol.

Your options seem to be:
- don't use dollar quoting
- use the 7.4 driver which doesn't try to split queries
- teach the driver about dollar quoting

Making the driver aware of dollar quoting is clearly the best (and
hardest) option.

Kris Jurka

[Oliver Jowett]

Kris Jurka wrote:

> Your options seem to be:
> - don't use dollar quoting
> - use the 7.4 driver which doesn't try to split queries
> - teach the driver about dollar quoting

It's a pity the grammar doesn't let you specify the function body as a
parameter, then you could just use a PreparedStatement and avoid the
quoting problems altogether..

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Kris Jurka]

On Wed, 15 Sep 2004, Oliver Jowett wrote:

> Kris Jurka wrote:
>
> > Your options seem to be:
> > - don't use dollar quoting
> > - use the 7.4 driver which doesn't try to split queries
> > - teach the driver about dollar quoting
>
> It's a pity the grammar doesn't let you specify the function body as a
> parameter, then you could just use a PreparedStatement and avoid the
> quoting problems altogether..

Yes and no. The problem with that approach is that you have to know what
you're doing ahead of time instead of just blindly passing a query string
into Statement.execute(). Consider an admin interface (or say psql)
written in java, it should be able to take an arbitrary sql string and run
it without problem. This is just another case of the driver not correctly
lexing/parsing a query. Also note that the current code does not
correctly handle single quotes that are escaped with backslashes.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Oliver Jowett]

Kris Jurka wrote:
>
> On Wed, 15 Sep 2004, Oliver Jowett wrote:
>
>
>>Kris Jurka wrote:
>>
>>
>>>Your options seem to be:
>>> - don't use dollar quoting
>>> - use the 7.4 driver which doesn't try to split queries
>>> - teach the driver about dollar quoting
>>
>>It's a pity the grammar doesn't let you specify the function body as a
>>parameter, then you could just use a PreparedStatement and avoid the
>>quoting problems altogether..
>
>
> Yes and no. The problem with that approach is that you have to know what
> you're doing ahead of time instead of just blindly passing a query string
> into Statement.execute().

Well, yes. But it'd still be useful.

COMMENT ON is another example where the grammar doesn't let you pass
data (not needed at parse/plan time) as a parameter. Our app has to do
its own escaping of comment text and glue it into the COMMENT ON query.
Yuck.

> Consider an admin interface (or say psql)
> written in java, it should be able to take an arbitrary sql string and run
> it without problem.

Depending on the exact interface, the client code may need exactly the
same parsing logic as the driver, e.g. to determine the end of a
multiline query.

Part of the problem is that JDBC is not really set up for
multiple-statement queries; they're a driver-specific extension that
doesn't interact wonderfully with the standard API.

> This is just another case of the driver not correctly
> lexing/parsing a query. Also note that the current code does not
> correctly handle single quotes that are escaped with backslashes.

Sure. It looks like double quotes also aren't handled correctly. I think
that parsing code got inherited from the pre-v3 version.

-O

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