4

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?

  • .... -t\\n....env\\n...,.name)\\n....env'\\n.... where \n are literal newlines – mikeserv Jan 15 '16 at 11:13
5

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' | ...
  • Oh that's very neat, thank you, I think I'd rather do it that way. May I just ask though, what purpose do the three elipses fulfill, that is, what is their script meaning? – HelenH Jan 15 '16 at 14:40
  • 1
    Sorry, the ellipses are representative of the rest of your pipeline, ie, your egrep. – Otheus Jan 18 '16 at 10:40
  • For anyone who wants to learn more about that syntax: This is called a here document. – Florian Brucker Mar 9 '16 at 8:50
  • Why does the pipe (and rest of the pipeline) go inside the string? – Adam Barnes Mar 22 at 18:37
  • The | follows your command. the "here" document starts immediately on the next line of the pipeline. So, the pipeline isnt going 'inside the string' (This makes it impossible to have a multiline statement AND a heredoc simultaneously) – Otheus Mar 23 at 18:42
0

Apologies, I just tried using the standard backslash character and it worked, so:

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

I had been told by a DBA colleague that this would not work, since it's an sql command and not a 'standard' Unix command. Seems I should have tried it for myself before believing!!

  • It's just a plain old double-quoted string, which is treated as such by the shell. Nothing magical about it just because it contains SQL! – D_Bye Jan 15 '16 at 11:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.