Re: Problem with dollar-quoted CREATE OR REPLACE FUNCTION

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