Edson Richter <edsonrichter@xxxxxxxxxxx> 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
