Re: Problem with dollar-quoted CREATE OR REPLACE FUNCTION

[Kris Jurka <books(at)ejurka(dot)com>]

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