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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply