Postgres $-quoted strings not supported

Hello, all

It has been nine years since this bug was reported here in thread titlded declaring functions in PostgreSQL (I am not allowed to use links), but it is still unfixed: HeidiSQL 12.10.0.7000 (circa 2025) cannot handle Postgres dollar-quoted strings. For example, the following perfectly legitimate anonymous block:

DO $$ begin commit; end $$;

works in psql—the native Postgres client, as well as in other third-party GUI clients, e.g. DBeaver, but HeidiSQL* fails to execute it, complaining about an unterminated $-string.

Observe that PostgreSQL supports sever-side parsing, so that the client may send the whole text as is. See the documentation to PQgetResult, to which I should like to post a link but the forum won't let me.

You should be able to set a different query delimiter than the semicolon, for instance //, then do it that way:

DO $$ begin commit; end $$//

You can change the delimiter using the button on the main toolbar with the semicolon on it (second-last one). Alternatively, set it per client query:

DELIMITER //
DO $$ begin commit; end $$//

Could you please test that? I think the problem is anyway not the dollar quoted string itself, but the fact that HeidiSQL cuts at semicolon.

Indeed. With

DELIMITER //
DO $$ begin commit; end $$//

I get a different error in HeidiSQL:

cannot begin/end transactions in PL/pgSQL

which makes perfect sense.

I think the problem is anyway not the dollar quoted string itself, but the fact that HeidiSQL cuts at semicolon.

Then there is still a bug, but not the one I thought, right?

No, I cannot see a bug in HeidiSQL here. As said, the semicolon can be customized. And the error "cannot begin/end transactions in PL/pgSQL" is something server-sided. begin/commit is probably not allowed between the dollar notations. However, on my local PostgreSQL 15 server that works without error.

ansgar,

No, I cannot see a bug in HeidiSQL here.

The bug is simple. HeidiSQL incorrectly parses the following code:

DO $$ begin commit; end $$;

complaining about an unterminated $-quoted string. This is not the correct and expected behaviour, as can be seen from executing the same code in the native client, psql. The $-quoted string above is pretty well terminated.

As said, the semicolon can be customized.

Even so, it should execute the original unmodified code as well.

And the error "cannot begin/end transactions in PL/pgSQL" is something server-sided.

Yes, that is a sever-side error, which means that HeidiSQL correctly exeuctes this code:

DELIMITER //
DO $$ begin commit; end $$//

begin/commit is probably not allowed between the dollar notations. However, on my local PostgreSQL 15 server that works without error.

Yes, this has changed since Postgres 9.

Thank you @ant.

In the meantime I understood a part of of these double-dollar-quotes, and that my suggestion is just a workaround. Also I found issue #1326 is basically reporting the same. Is it correct to say HeidiSQL should not separate queries (by semicolon) between $$ quotes ? Is there more important stuff to know about the dollar quotes?

ansgar,

In the meantime I understood a part of of these double-dollar-quotes, and that my suggestion is just a workaround.

Yes.

Also I found issue #1326 is basically reporting the same.

I seems so, yes.

Is it correct to say HeidiSQL should not separate queries (by semicolon) between $$ quotes?

As far as I understand, yes. But I am not a Postgres develper, nor a user of their API, so I cannot be 100% certain. I consulted about this problem in their IRC channel:
irc://irc.libera.chat:6697/#postgresql,
where they pointed out that this RDBMS supports complete server-side parsing, so that the client need not parse anything at all.

Is there more important stuff to know about the dollar quotes?

The best advice that I can give you for know is to consult the Postgres community—either vie the IRC channel mentioned above or the mailing list. The people there are very professinal and helpful. I am sorry that I cannot provide any more specific help.

I wrote:

where they pointed out that this RDBMS supports complete server-side parsing, so that the client need not parse anything at all.

...Now that I can post URLs, but cannot edit my posts, I'll add that the server-side parsing is menioned, e.g. for PQexec. It says:

The command string can include multiple SQL commands (separated by semicolons). Multiple queries sent in a single PQexec call are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions.

And the refers to a section callled [Multiple Statements in a Simple Query*](Multiple Statements in a Simple Query)

Yikes. PQexec is actually a link, but I got the link to Multiple Statements in a Simple Query wrong. Fixed here. I beg pardon for the noise.

Latest nightly build now supports $$ strings in PostgreSQL, and semicolon in such strings is ignored.

See issue #1326 for more details.