Re: BUG #15756: Seemingly inconsistent behavior of SUBSTRING(string FROM pattern FOR escape) function

On Mon, Apr 15, 2019 at 12:02 PM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:

> when psql command line tool is to be used:
> psql --username=postgres --no-password --command="SELECT
> SUBSTRING('+380481234567' FROM 5 FOR 9), SUBSTRING('+380481234567' FROM
> '%#"[0-9]{9}#"' FOR '#')"
> But here something strange occurs! The latter function invocation

You are not using psql here to send the same query to the server. You
are using the shell to invoke sql with some arguments, and the shell
does quote parsing and similar things. To make the correct example you
should do the same thing you do with pgAdmin, start psql and then type
the query to it.

Windows /unix shells have some strange quoting behaviours which I'm
not completely familiar with, and you haven't told us which one you
use. If you are using the classic bash it is going to remove your
quotes, if you send "aaa'bbbb"cccc"dddd'eeee" to bash it is going to
produce <aaa'bbbcccddd'eee>, as quotes do not nest, and that is what
psql is going to see.

Start with the simple thing, test firing psql and typing the uery
inside it, then, if it still fails, report that result, else elaborate
from this ( try more elaborate quoting, sending the query in a file /
stdin, etc.. ).

Francisco Olarte.