Unable to use $$ when creating a function in PostgreSQL

[fjtc]

Describe the bug
Antares will send a ill formatted command to the database if you try to create a function that contains $$ or any of its variants. If you get the example from https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT:

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
$$ LANGUAGE plpgsql;

Results in the following error message:

error: syntax error at or near "C$$E$$T$$"

To Reproduce
Steps to reproduce the behavior:

1. Open a connection to an existing PostgreSQL database;
2. Create a new SQL window;
3. Paste the above example and run it;
4. See error message described above;

Expected behavior

Since this is a valid PostgreSQL function definition, it should work as expected if the given function does not exist. The example function will be created but will not work if the table user does not exist (but this is irrelevant to this bug).

Screenshots

Not required.

Application (please complete the following information):

• App client: Antares SQL
• App version: 0.7.35
• Installation source: Flathub

Environment (please complete the following information):

• OS name: Ubuntu
• OS version: 25.04
• DB name: PostgreSQL
• DB version: 16.x but fails with newer versions too

Additional context

If you look at the PostgreSQL log, you will find the actual syntax error as:

ERROR:  syntax error at or near "C$$E$$T$$" at character 1
STATEMENT:  C$$E$$T$$ $$U$$C$$I$$N$$g$$t$$u$$e$$i$$($$s$$r$$a$$e$$t$$x$$)$$R$$T$$R$$S$$i$$t$$A$$ $$$
   #print_strict_params on
   DECLARE
   userid int;BEGIN
       SELECT users.userid INTO STRICT userid
           FROM users WHERE users.username = get_userid.username;RETURN userid;END;$$$ LANGUAGE plpgsql;
...

Looking at it, it is easy to see that the presence of $$ is making Antares to replace all even characters of the first part of the command with $$. I made a few experiments with other variants like $something$ and the effect is very similar but not exactly the same:

C$something$T$something$e$something$m$something$T$something$S$something$g$
...

It appears that the presence of $$ is triggering some kind of unwanted string transformation that result in the broken command being sent to PostgreSQL.

Fortunately, there is an workaround but it is a bit cumbersome. You can put the code of the function between single quotes:

CREATE FUNCTION get_userid(username text) RETURNS int
AS '
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
' LANGUAGE plpgsql;

but is is quite error prone because all single quotes in the code will need to be escaped to double single quotes (e.g. 'a := ''string''').