Обсуждение: How to double-quote a double quoted identifier?

Edson Richter <edsonrichter@hotmail.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@Q} construct.

$ SQLCMD='CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'
$ echo $SQLCMD
CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;
$ echo ${SQLCMD@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@Q}"
$ echo $SHCMD
psql -h 127.0.0.1 -d test_db -p 5432 -c 'CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'
$ echo ${SHCMD@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@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"
SCHEMApg_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

De: Tom Lane <tgl@sss.pgh.pa.us>

Enviado: segunda-feira, 15 de junho de 2020 22:24
Para: Edson Richter <edsonrichter@hotmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Assunto: Re: How to double-quote a double quoted identifier?

 

Edson Richter <edsonrichter@hotmail.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@Q} construct.

$ SQLCMD='CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'
$ echo $SQLCMD
CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;
$ echo ${SQLCMD@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@Q}"
$ echo $SHCMD
psql -h 127.0.0.1 -d test_db -p 5432 -c 'CREATE EXTENSION "uuid-ossp" SCHEMA pg_catalog;'
$ echo ${SHCMD@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@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