Shell – how to split long psql command in bash script over several lines

postgresqlshell-script

I have a long psql command to execute within a bash shell script, and I am wondering what the correct way is to split it across several lines? The standard Unix script 'split line' backslash character doesn't work. Within the postgres environment of course you could keep going line after line and the command doesn't get processed until you enter the closing semi-colon, but you don't use that within a shell script.

My command is this:

sudo -u postgres /opt/puppet/bin/psql puppetdb -t -c "select certname, r.value as role, e.value as env from certname_facts r join certname_facts e using (certname) where r.name = 'role' and e.name = 'env' order by role,env,certname" | grep -v "^$" > /home/ansible/inventory_list

I don't want to change the command, it all works perfectly when entered manually, but I need to know the correct way to make this into a split-line entry, something like:

sudo -u postgres /opt/puppet/bin/psql puppetdb -t
-c "select certname, r.value as role, e.value as env
from certname_facts r join certname_faces e using (certname)
where r.name = 'role' and e.name = 'env'
order by role,env,certname" | grep -v "^$" > /home/ansible/inventory_list

Any suggestions, please?

Best Answer

Nothing wrong with splitting it up with backlashes as you showed. However, it's generally better to send the SQL via stdin. For postgres, this is especially true, since the '-c' option is fixed to return output from only one command, whereas accepting commands from stdin, you can stack as many commands together as you like. So, you would do something like:

sudo -u postgres /opt/puppet/bin/psql puppetdb -t  <<SQL | ...
  select certname, r.value as role, e.value as env
      from certname_facts r join certname_faces e using (certname)
      where r.name = 'role' and e.name = 'env'
      order by role,env,certname
SQL

Bash variables might get interpolated here. To avoid that, quote the first instance of SQL:

sudo -u postgres /opt/puppet/bin/psql puppetdb -t <<'SQL' | ...
Related Question