RE: How to double-quote a double quoted identifier?

De: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Enviado: segunda-feira, 15 de junho de 2020 22:24
Para: Edson Richter <edsonrichter(at)hotmail(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Assunto: Re: How to double-quote a double quoted identifier?

Edson Richter <edsonrichter(at)hotmail(dot)com> writes:
> How do I remote execute que following command:
> CREATE EXTENSION "uuid-ossp"
> SCHEMA public
> VERSION "1.1";

> I'm using PostgreSQL 9.6, Linux x64, and bash.

> I've tried the following:

> ssh -l postgres -2 -C -p 2022 192.168.0.70 "psql -h 127.0.0.1 -d test_db -p 5432 -c \"CREATE EXTENSION \\\\""uuid-ossp\\\\"" SCHEMA pg_catalog;\" "

Yeah, nesting quoting levels in shell is kind of a bear.

> I know this is a kind of "bash-psql" issue, but I would appreciate if anyone could share experience with this.

Since you're using bash, you can get bash to do the work for you,
via multiple iterations of the ${variable(at)Q} construct.

$ SQLCMD='CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'
$ echo $SQLCMD
CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;
$ echo ${SQLCMD(at)Q}
'CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'
-- not too exciting so far, but wrap it into another layer of quoting:
$ SHCMD="psql -h 127.0.0.1 -d test_db -p 5432 -c ${SQLCMD(at)Q}"
$ echo $SHCMD
psql -h 127.0.0.1 -d test_db -p 5432 -c 'CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'
$ echo ${SHCMD(at)Q}
'psql -h 127.0.0.1 -d test_db -p 5432 -c '\''CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'\'''
-- and now your answer is:
$ echo ssh -l postgres -2 -C -p 2022 192.168.0.70 ${SHCMD(at)Q}
ssh -l postgres -2 -C -p 2022 192.168.0.70 'psql -h 127.0.0.1 -d test_db -p 5432 -c '\''CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'\'''

You could, of course, just use the end result of that -- but it's
probably better to have a shell script recalculate it on the fly
given the desired SQL command as input.

regards, tom lane
Ingenious!
Thanks a lot!

Edson Richter