# Filtering database names from RETURN VALUES of postgresql\_info **URL:** \ **Category:** Get Help\ **Created:** [October 8, 2024, 7:09am UTC](https://forum.ansible.com/t/filtering-database-names-from-return-values-of-postgresql-info/9985 "2024-10-08T07:09:03Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\ **Post date:** [October 8, 2024, 7:09am UTC](https://forum.ansible.com/t/filtering-database-names-from-return-values-of-postgresql-info/9985/1 "2024-10-08T07:09:03Z")
I am trying to GRANT pg\_read\_all\_date to a certain role on all existing databases exept `postgres`. `template0,``template1`. To get those infos I do ```auto - name: get postgres infos hosts: "{{ targets | default('postgres') }}" tasks: - name: get postgres infos community.postgresql.postgresql_info: login_unix_socket: /tmp filter: 'databases' become: true become_user: postgres register: pg_info - name: show database_names debug: msg: "{{ pg_info }}" ``` how do I filter the database names from that wall of text retrun via `pg_info` in order to use those in `when` clauses further on? ---
**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\ **Post date:** [October 8, 2024, 8:47am UTC](https://forum.ansible.com/t/filtering-database-names-from-return-values-of-postgresql-info/9985/2 "2024-10-08T08:47:31Z")
I get a notion that somehow the solution on approaching such a situation may be lying in this [dict\_lookup article](https://docs.ansible.com/ansible/latest/collections/ansible/builtin/dict_lookup.html) Just applying the very simple example to the very complex usecase still provides some headaces. ---
**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\ **Post date:** [October 8, 2024, 8:09pm UTC](https://forum.ansible.com/t/filtering-database-names-from-return-values-of-postgresql-info/9985/3 "2024-10-08T20:09:34Z")
> [@dulhaver](#): > > how do I filter the database names Looking at [how I have done this](https://git.coop/webarch/postgresql/-/blob/3d7279c0b23baf5823a5417b1edb2802064fff99/tasks/info.yml#L56) I think this might work? ```yaml - name: show database_names ansible.builtin.debug: msg: "{{ pg_info.databases.keys() }}" ``` ---
**Author:** ![dulhaver](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/dulhaver/32/1061_2.png) [@dulhaver](https://forum.ansible.com/u/dulhaver)\ **Post date:** [October 12, 2024, 6:39pm UTC](https://forum.ansible.com/t/filtering-database-names-from-return-values-of-postgresql-info/9985/4 "2024-10-12T18:39:35Z")
so since 4 days I am pulling out my hair while taking this a step further (and actually doing something useful with the output). The relevant part of my playbook is: ```yaml - name: Set a fact for the existing PostgreSQL databases ansible.builtin.set_fact: postgresql_dbs_existing: "{{ pg_info.databases.keys() }}" - name: debug 'postgresql_dbs_existing' ansible.builtin.debug: msg: "{{ postgresql_dbs_existing }}" ``` I my concrete case (`stdout_callback: yaml`), I am getting this: ```auto [gwagner@rd1985 ad_hoc_playbooks]$ play pg_get_info.yml -l '*rd5792*' PLAY [get postgres infos] ******************************************************************************************* TASK [get postgres infos] ******************************************************************************************* ok: [dvzsn-rd5792.local] TASK [Set a fact for the existing PostgreSQL databases] ************************************************************* ok: [dvzsn-rd5792.local] TASK [debug 'postgresql_dbs_existing'] ****************************************************************************** ok: [dvzsn-rd5792.local] => msg: - postgres - template1 - prodmin_coo_store_001 - prodmin_im_coo_store_001 - test_db ``` My goal actually is to execute subsequent tasks on all databases expect `postgres`, `template0`, `template1`. So I need to use the list (it’s a `list`, right?) I am getting for `postgresql_dbs_existing` and apply some filtering to it. The way I am trying to use is a `when: ` clause. ```yaml - name: list db names with loop ansible.builtin.debug: msg: "{{ db_name }}" loop: "{{ postgresql_dbs_existing }}" loop_control: loop_var: db_name where: "'postgres' not in 'db_name'" ``` ```auto ERROR! conflicting action statements: ansible.builtin.debug, where The error appears to be in '/home/gwagner/repos/ad_hoc_playbooks/pg_get_info.yml': line 30, column 7, but may be elsewhere in the file depending on the exact syntax problem. The offending line appears to be: - name: list db names with loop ^ here ``` ---
**Author:** ![chris](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/chris/32/293_2.png) [@chris](https://forum.ansible.com/u/chris)\ **Post date:** [October 13, 2024, 8:19am UTC](https://forum.ansible.com/t/filtering-database-names-from-return-values-of-postgresql-info/9985/5 "2024-10-13T08:19:32Z")
The error you pasted above is caused by the typo, `where` rather than `when`. Also the `in` test is [a check for an item in a list](https://jinja.palletsprojects.com/en/3.1.x/templates/#jinja-tests.in) not a test for the value of a variable, so in the example you provided you would want to test for a negative string match: ```auto - name: list db names with loop ansible.builtin.debug: var: db_name loop: "{{ postgresql_dbs_existing }}" loop_control: loop_var: db_name when: db_name != "postgres" ``` You can use `debug_type` to check the type of the variable: ```auto - name: list db name types with loop ansible.ansible debug_type.debug: msg: >- db_name is a variable with the type: {{ db_name | ansible.builtin.type_debug }} loop: "{{ postgresql_dbs_existing }}" loop_control: loop_var: db_name ``` However I’d omit the names earlier with the `set_fact` so a `when` is not needed on the loop, something like this: ```yaml - name: Set a fact for the existing PostgreSQL databases ansible.builtin.set_fact: postgresql_dbs_existing: >- pg_info.databases.keys() | reject('regex', '^postgres$') | regect('regex', '^template[0|1]$') ``` Rather than skipping the ones you don’t want in the loop. ---
**Author:** ![system](https://us1.discourse-cdn.com/ansible/original/1X/1882d9a01da5d8996a862359b0913cc568a39b84.svg) [@system](https://forum.ansible.com/u/system)\ **Post date:** [November 12, 2024, 8:20am UTC](https://forum.ansible.com/t/filtering-database-names-from-return-values-of-postgresql-info/9985/6 "2024-11-12T08:20:23Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.