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?

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?

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';
                                                           ^

As suggested by @β.εηοιτ.βε , it was sufficient to replace the hardcoded value of the date in jinja2 templates with the jinja2 "expression tags" {{ ... }}

select * from mytable where date='{{ input_date }}'

also, as indicated by @larsks, the query in the jinja2 template must not end with column ( ; )