Using PostgreSQL both with (not only) SQL Workbench – how to apply alternate delimiters

Posted on

Question :

I have created a PostgreSQL database. Now I try to work with it in SQL Workbench, but I have a problem with the way it understands statement delimiters. The documentation on alternate delimiters sais that SQL Workbench takes semicolon as the end of statement, which causes problems for functions and similar code blocks with more than one semicolon. That’s why there’s the ‘alternate delimiter’ option. However, the default alternate delimiter (/) makes the code invalid elsewhere – I picked SQL Developer for a workaround and as an alternate front-end, I can’t sacrifice pgAdmin for it and I can’t afford maintaining two versions of my source code just to avoid this limitation.

I tried to set --/ as the alternate delimiter instead – this disables the default ‘/’, but it doesn’t apply – seems that SQL Workbench can’t handle multi-char delimiters.

Is there any way how to solve this problem? Is there any char that wouldn’t make me trouble in pgAdmin but would be suitable for SQL Workbench alternate delimiter? Or is there any way to enable alternate delimiters consisting of some harmless char sequence?

EDIT: I tried to define the alternate delimiter both at global and connection settings, and with and without the “single line” option, and no success.

Answer :

I have asked in the SQL Workbench forum and Thomas Kellerer (author of SQL Workbench) helped me. This is a summary of his answer.

First, my sequence was wrong because -- is a comment, but the delimiter must be a valid (not commented out) character or a valid sequence of characters. This makes finding something suitable for a delimiter in SQL Workbench and still not breaking sql code for psql and pgAdmin impossible.

However, there is a workaround: to execute the script through following command:

WbInclude -file=/some/dir/my_function.sql -delimiter=XXXXXX;

That XXXXXX can be anything that is not in the code. This way you can only execute scripts, not edit them, but that’s sufficient for me. Currently, there’s no better way to operate the code both in and out SQL Workbench.

Leave a Reply

Your email address will not be published. Required fields are marked *