Question 182744 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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 palybook 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 case in my ansible playbook there would be a variable defined 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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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 ( ; )


  2. 0

    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';
                                                           ^
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search