Dollar-quotes failing with JDBC

Question

I have to write a function in PLPGSQL but I have problem with the function body quoted with dollar-quoting. Using the first tutorial:

CREATE FUNCTION inc(val integer) 
RETURNS integer AS 
$BODY$
BEGIN
    RETURN val + 1;
END; 
$BODY$
LANGUAGE PLPGSQL;

I get an error:

unterminated dollar-quoted string at or near $$

Searching on google I just found it's a JDBC Driver problem but I cannot update it. So I have tried to change the DELIMITER to remove $$:

DELIMITER ++;
CREATE FUNCTION inc(val integer) 
RETURNS integer AS 
++BODY++
BEGIN
    RETURN val + 1;
END; 
++BODY++
LANGUAGE PLPGSQL;
DELIMITER ;

The command doesn't return any error but function doesn't exists when I try to call it:

select inc(4);

enter image description here

What am I missing?

It seems your SQL client can't handle the dollar quoting. Which SQL client are you using? (And the "dollar quotes" are **not** a delimiter, they are an alternative way of [writing a string constant](https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING)) — , Jun 20 '18 at 08:10
It's most certainly an issue with your client not handling `$` signs. Your function looks just fine. — Jim Jones, Jun 20 '18 at 08:50

Erwin Brandstetter · Answer 1 · 2018-06-26T23:20:09.373

The underlying problem is JDBC's inability to deal with dollar-quotes.
I think this was fixed with JDBC version 9.4.1208 (2016-02-16).

See:

You can avoid the problem by using plain quotes for the simple case:

CREATE FUNCTION inc(val integer) 
  RETURNS integer AS 
'
BEGIN
   RETURN val + 1;
END 
' LANGUAGE plpgsql;

See:

1 Answers1