How can I define a variable in a SQL query saved in a jinja2 template and have it substituted with a value when the query is run by ansible?

Question

In my Ansible playbook, there is a task which runs a SQL query saved in a Jinja2 template on my database.

- hosts: "{{ hosts_list }}"
  gather_facts: no

  vars_files:
    - vars/main.yml
    - vars/my_hosts.yml
    - vars/target_date.yml

  tasks:
    - name: generate report
      community.postgresql.postgresql_copy:
        login_host: '{{ db_host }}'
        login_user: '{{ db_username }}'
        login_password: '{{ db_password }}'
        db: '{{ db_database }}'
        port: '{{ db_database_port }}'
        src: "{{ lookup('template', 'my_report_query.sql.j2') }}"
        copy_to: /tmp/my_report.csv
        options:
          format: csv
          delimiter: ';'
          header: yes

The query is something like:

select * from mytable where date='2023-02-03';

Now, I would like to pass to the query a value of the date which is defined in my Ansible playbook.

So, in the case there would be a variable defined in my Ansible playbook as

input_date: "2023-05-03"

saved in file playbooks/my_playbook/vars/target_date.yml

How can I edit my playbook and Jinja2 template in order to pass that variable to the query?

Answer 1

In Ansible, you don't pass variables to your templates -- a template has access to all variables that are currently in scope. In other words, you just need to make sure that you've sourced that target_date.yml file. For example, here we're doing that with the include_vars module:

- hosts: localhost
  gather_facts: false
  vars:
    db_host: localhost
    db_username: example_user
    db_password: example_pass
    db_database: example_db
  tasks:
  - name: read vars file
    include_vars:
      file: vars/target_date.yml

  - name: generate report
    community.postgresql.postgresql_copy:
      login_host: '{{ db_host }}'
      login_user: '{{ db_username }}'
      login_password: '{{ db_password }}'
      db: '{{ db_database }}'
      port: '{{ db_database_port|default(5432) }}'
      src: "{{ lookup('template', 'my_report_query.j2.sql') }}"
      copy_to: /tmp/my_report.csv
      options:
        format: csv
        delimiter: ';'
        header: yes

We could also read in the vars file:

• By using the -e option on the ansible-playbook command line:

  ansible-playbook playbook.yaml -e @vars/target_date.yaml
  

• By using the vars_files option in the play:

  - hosts: localhost
    vars_files:
    - vars/target_date.yml
  

• By setting the input_date variable in an appropriate file in the group_vars or host_vars directories.

Etc.

---

Note that for your playbook to work, you'll need to drop the ; in your query template. With the terminal semicolon your copy task will fail with:

Cannot execute SQL 'COPY (select * from mytable where date='2023-05-03';
) TO '/tmp/my_report.csv' (format csv, delimiter ';', header True)': syntax error at or near ";"
LINE 1: COPY (select * from mytable where date='2023-05-03';
                                                           ^