How to display an ansible variable.query_result into a semicolon separated CSV with header

I am building an ansible playbook.

I would like to run a \COPY (...) TO file; query on a remote DB and dump the results into a semicolon (;) separated CSV, with header.

Normally I would do it with module community.postgresql.postgresql_copy

- name: extract my table from DB
  community.postgresql.postgresql_copy:
    login_host: '{{ db_host }}'  # remote host
    login_user: '{{ db_username }}'
    login_password: '{{ db_password }}'
    db: '{{ db_database }}'
    port: '{{ db_database_port }}'
    src: "{{ lookup('template', 'extract_my_table.sql.j2') }}"
    copy_to: "{{ log_base_path }}/{{ csv_file }}"
    options:
      format: csv
      delimiter: ';'
      header: yes

However, even if I can access the remote DB, I cannot SSH login or access the disk of the server on which the DB is.
So I am saving the result of the query as var and then dumping it into a csv file on the host machine.

- name: extract my table from DB
  community.postgresql.postgresql_query:
    login_host: '{{ db_host }}'
    login_user: '{{ db_username }}'
    login_password: '{{ db_password }}'
    db: '{{ db_database }}'
    port: '{{ db_database_port }}'
    query: "{{ lookup('template', 'extract_my_table.sql.j2') }}"

  register: my_table__qres

Now, my_table__qres.query_result is a list of dictionaries having keys equal to the fields extracted by the query (every dictionary is a row).

How can I display the my_table__qres.query_result content into a new semicolon (;) separated CSV, with header?

Solution

Try the following by creating a Jinja2 template (my_table_template.j2) that iterates over the query result and formats it into CSV format.

{% for row in my_table__qres.query_result %}
{{ row.values() | join(';') }}
{% endfor %}

Then, use the template module in your playbook to apply this template and create the CSV file.

- name: Generate CSV from query result
  ansible.builtin.template:
    src: my_table_template.j2
    dest: "{{ log_base_path }}/{{ csv_file }}"
  vars:
    my_table__qres: "{{ my_table__qres }}"