How to execute psql command using ansible with variables

Question

I am trying to run below task but it gives error.

-
  name: Check if Schema exist
  shell: "psql -h {{ dbserver }} -U {{ dbuser } {{ dbname }} -c '\dt' | grep -c public"
  environment:
    PGPASSFILE: "{{ pgpass_file }}"
  register: table_count

-
  name: Check if admin user exists or not
  shell: "psql -h {{ dbserver }} -U {{ dbuser } {{ dbname }} -c 'select count(*) from user where name='adam'' | awk NR==3"
  environment:
    PGPASSFILE: "{{ pgpass_file }}"
  register: admin_count
  when: table_count.stdout != "0"

Below is the error I am getting.

The offending line appears to be:
      name: Check if Schema exist
      shell: "psql -h {{ dbserver }} -U {{ dbuser } {{ dbname }} -c '\dt' | grep -c public"
                                                                       ^ here

Now I can try to replace these variables and use it, it might work, but how do I get this working, I tried -c "\dt" (using '' as escape charater) but that didn't work as well

Answer 1

The problem is actually the " that (needlessly) wraps your shell: scalar, since \ is interpreted inside of double-quoted scalar values

One can see this in action:

$ printf 'alpha: beta \\dt\n' | yaml2json
{"alpha":"beta \\dt"}
$ printf 'alpha: "beta \\dt"\n' | yaml2json
Error: Cannot parse as YAML (while scanning a double-quoted scalar
  in "<byte string>", line 1, column 8:
    alpha: "beta \dt"
           ^
found unknown escape character 'd'

Thus, you have a couple of paths forward:

1. just remove the double quotes, since there's nothing in your shell: that needs protection from the yaml parser
2. move the interior \dt to a single quoted vars: and let ansible quote it for you
3. Do what P.... said and take your chances with the "how many backslashes do I need for this?" game

  shell: psql -h {{ dbserver }} -U {{ dbuser }} {{ dbname }} -c '\dt' | grep -c public

or

  shell: psql -h {{ dbserver }} -U {{ dbuser }} {{ dbname }} -c {{ psql_c | quote }} | grep -c public
  vars:
    psql_c: '\dt'

You'll almost certainly want to take advantage of that vars: trick for the next question you're going to ask about why the name: Check if admin user exists or not doesn't do what you expect due to the crazy quoting of your -c in that statement, too

  shell: "psql -h {{ dbserver }} -U {{ dbuser }} {{ dbname }} -c {{ psql_c | quote }} | awk NR==3"
  vars:
    psql_c: >-
      select count(*) from user where name='adam'

---

p.s. I took the liberty of fixing your mismatched mustaches typo in dbuser }}, too, while I was at it