Ansible - module postgresql_query : Input SQL file with multiple queries | output query_result only latest query

[OlivierRoger]

SUMMARY

Module : postgresql_query
Input File.sql with many queries
Return Values query_result : only show latest query result

I want full result of my SQL File not only latest query in SQL file

ISSUE TYPE

• Help

COMPONENT NAME

Module : postgresql_query
Return Values query_result

ANSIBLE VERSION

ansible 2.8.2
config file = /etc/ansible/ansible.cfg
configured module search path = [u'/home/deployer/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /usr/bin/ansible
python version = 2.7.5 (default, Aug 7 2019, 00:51:29) [GCC 4.8.5 20150623 (Red Hat 4.8.5-39)]

##### CONFIGURATION

##### OS / ENVIRONMENT
CentOS Linux release 7.6.1810 (Core)

##### STEPS TO REPRODUCE
TBF_03.sql : select u.uti_id_ldap, u.id_prf from p_perimetre p, t_utilisateur u where u.id_prm = p.id_prm and p.prm_cod = 'LCFA'; SELECT NOW();

hosts: localhost
tasks:

  name: db_management {{ database_type }} | Run queries from SQL script
  postgresql_query:
	db: "{{ var_env_dict['SOCLE_'+app|upper+'DB_NAME'] }}"
	login_host: "{{ var_env_dict['SOCLE'+app|upper+'DB_HOST'] }}"
	port: "{{ var_env_dict['SOCLE'+app|upper+'DB_PORT'] }}"
	login_user: "{{ var_env_dict['SOCLE'+app|upper+'DB_USER'] }}"
	login_password: "{{ var_env_dict['SOCLE'+app|upper+'_DB_PWD'] }}"
	path_to_script: "TBF_03.sql"
	register: query_res

  name: db_management {{ database_type }} | Query result success
  debug:
  msg:
	- "SQL Filename {{ query_res.item }}"
	- "SQL Results {{ query_res.query_result}}"
	- "SQL Query {{ query_res.query }}"
	- "SQL CountRow {{ query_res.rowcount}}"
	- "SQL Status FAIL : {{ query_res.failed }}"


##### EXPECTED RESULTS
dbname=> select u.uti_id_ldap, u.id_prf from p_perimetre p, t_utilisateur u where u.id_prm = p.id_prm and p.prm_cod = 'LCFA'; SELECT NOW();
 uti_id_ldap | id_prf
-------------+--------
 Z9999       |      7
 Z9999       |      6
 res26       |      6
 trocf       |      7
[...]
(30 rows)
              now
-------------------------------
 2020-08-27 15:56:34.451282+02
(1 row)



##### ACTUAL RESULTS
  "query": "select u.uti_id_ldap, u.id_prf from p_perimetre p, t_utilisateur u where u.id_prm = p.id_prm and p.prm_cod = 'LCFA'; \nSELECT NOW();\n",
        "query_result": [
            {
                "now": "2020-08-27T16:32:33.236086+02:00"
            }
        ],
        "rowcount": 1,
        "statusmessage": "SELECT 1",

[ansibullbot]

Files identified in the description:

• plugins/modules/database/postgresql/postgresql_query.py

If these files are inaccurate, please update the component name section of the description or use the !component bot command.

click here for bot help

[ansibullbot]

cc @Andersson007 @Dorn- @amenonsen @andytom @archf @jbscalia @kostiantyn-nemchenko @matburt @nerzhul @sebasmannem @tcraxs @wrouesnel
click here for bot help

[Andersson007]

@OlivierRoger hi, thanks for reporting this, I'll look at what we can do.

[Andersson007]

I added a couple of ret vals #886 . The changes are pretty significant, so i won't merge them without deep reviews and manual testing.

[Andersson007]

@OlivierRoger thanks for reporting this!
The PR has just been merged.
However we can't backport it to 2.9 and 2.8.
community.general 1.3.0 will be released ~at the end of November, so, you can install 2.10 and the collection manually since that.
Thanks!