postgresql_query: fix decimal handling (#73414)

Co-authored-by: Andrew Klychkov <andrew.klychkov@gmail.com>

3 files changed, 45 insertions, 1 deletions

diff --git a/changelogs/fragments/46-postgresql_query_fix_decimal_handling.yml b/changelogs/fragments/46-postgresql_query_fix_decimal_handling.yml
new file mode 100644
index 0000000000..7e229ef719
--- /dev/null
+++ b/changelogs/fragments/46-postgresql_query_fix_decimal_handling.yml
@@ -0,0 +1,2 @@
+bugfixes:
+- postgresql_query - fix decimal handling (https://github.com/ansible-collections/community.postgresql/issues/45).
diff --git a/lib/ansible/modules/database/postgresql/postgresql_query.py b/lib/ansible/modules/database/postgresql/postgresql_query.py
index d7b1e375d2..dc386d6a62 100644
--- a/lib/ansible/modules/database/postgresql/postgresql_query.py
+++ b/lib/ansible/modules/database/postgresql/postgresql_query.py
@@ -168,6 +168,8 @@ rowcount:
     sample: 5
 '''
 
+import decimal
+
 try:
     from psycopg2 import ProgrammingError as Psycopg2ProgrammingError
     from psycopg2.extras import DictCursor
@@ -300,8 +302,17 @@ def main():
     statusmessage = cursor.statusmessage
     rowcount = cursor.rowcount
 
+    query_result = []
     try:
-        query_result = [dict(row) for row in cursor.fetchall()]
+        for row in cursor.fetchall():
+            # Ansible engine does not support decimals.
+            # An explicit conversion is required on the module's side
+            row = dict(row)
+            for (key, val) in iteritems(row):
+                if isinstance(val, decimal.Decimal):
+                    row[key] = float(val)
+
+            query_result.append(row)
     except Psycopg2ProgrammingError as e:
         if to_native(e) == 'no results to fetch':
             query_result = {}
diff --git a/test/integration/targets/postgresql/tasks/postgresql_query.yml b/test/integration/targets/postgresql/tasks/postgresql_query.yml
index aca07442e2..4b7642ee92 100644
--- a/test/integration/targets/postgresql/tasks/postgresql_query.yml
+++ b/test/integration/targets/postgresql/tasks/postgresql_query.yml
@@ -468,3 +468,34 @@
     name: test_array_table
     state: absent
   when: postgres_version_resp.stdout is version('9.4', '>=')
+
+#############################################################################
+# Issue https://github.com/ansible-collections/community.postgresql/issues/45
+- name: Create table containing a decimal value
+  become_user: '{{ pg_user }}'
+  become: true
+  postgresql_query:
+    login_user: '{{ pg_user }}'
+    db: postgres
+    query: CREATE TABLE blabla (id int, num decimal)
+
+- name: Insert data
+  become_user: '{{ pg_user }}'
+  become: true
+  postgresql_query:
+    login_user: '{{ pg_user }}'
+    db: postgres
+    query: INSERT INTO blabla (id, num) VALUES (1, 1::decimal)
+
+- name: Get data
+  become_user: '{{ pg_user }}'
+  become: true
+  postgresql_query:
+    login_user: '{{ pg_user }}'
+    db: postgres
+    query: SELECT * FROM blabla
+  register: result
+
+- assert:
+    that:
+    - result.rowcount == 1