Multiple queries should return multiple results

[felipenazario]

SUMMARY

Sometime ago I opened the issue 312 and I it is marked as closed and resolved, but it keeps the same behavior as before. I still believe that if the script file has N SQL queries, it should return N results. But today it only returns the last SQL Query in the script file.

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_script

ANSIBLE VERSION

ansible [core 2.17.5]
  config file = /home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg
  configured module search path = ['/home/nazario/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/nazario/workspace_infra/MyCode/.pyenv_for_3.13.0/lib/python3.13/site-packages/ansible
  ansible collection location = /opt/ansible/collections:/home/nazario/workspace_infra/MyCode/ansible/linux/collections
  executable location = /home/nazario/workspace_infra/MyCode/.pyenv_for_3.13.0/bin/ansible
  python version = 3.13.0 (main, Oct  9 2024, 11:03:32) [GCC 7.5.0] (/home/nazario/workspace_infra/MyCode/.pyenv_for_3.13.0/bin/python)
  jinja version = 3.1.4
  libyaml = False

COLLECTION VERSION

Collection                               Version
---------------------------------------- -------
ansible.eda                              2.2.0  
ansible.netcommon                        7.1.0  
ansible.posix                            1.6.1  
ansible.utils                            5.1.2  
ansible.windows                          2.5.0  
chocolatey.chocolatey                    1.5.3  
community.crypto                         2.22.2 
community.digitalocean                   1.27.0 
community.docker                         3.13.1 
community.general                        9.5.0  
community.library_inventory_filtering_v1 1.0.1  
community.mysql                          3.10.3 
community.postgresql                     3.7.0  
community.windows                        2.3.0  
containers.podman                        1.16.1 
cyberark.conjur                          1.3.0  
dellemc.os6                              1.0.7  
dellemc.os9                              1.0.4  
digitalocean.cloud                       0.6.0  
kubernetes.core                          5.0.0  
oracle.oci                               5.3.0

CONFIGURATION

CALLBACKS_ENABLED(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = ['ansible.posix.timer', 'ansible.posix.profile_roles', 'ansible.posix.profile_tasks', 'ansible.builtin.oneline']
COLLECTIONS_PATHS(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = ['/opt/ansible/collections', '/home/nazario/workspace_infra/MyCode/ansible/linux/collections']
CONFIG_FILE() = /home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg
DEFAULT_FORKS(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = 10
DEFAULT_HOST_LIST(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = ['/home/nazario/workspace_infra/MyCode/ansible/linux/inventories/vagrant/hosts.ini']
DEFAULT_PRIVATE_ROLE_VARS(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = True
DEFAULT_ROLES_PATH(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = ['/opt/ansible/roles', '/home/nazario/workspace_infra/MyCode/ansible/linux/roles']
DEFAULT_TIMEOUT(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = 15
DEFAULT_VAULT_IDENTITY_LIST(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = ['prod@keys/ansible_vault/prod.sec']
DEPRECATION_WARNINGS(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = True
HOST_KEY_CHECKING(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = False
INTERPRETER_PYTHON(/home/nazario/workspace_infra/MyCode/ansible/linux/ansible.cfg) = auto

OS / ENVIRONMENT

uname -a

Linux localhost.localdomain 5.3.18-150300.59.106-default #1 SMP Mon Dec 12 13:16:24 UTC 2022 (774239c) x86_64 x86_64 x86_64 GNU/Linux

cat /etc/os-release

NAME="openSUSE Leap"
VERSION="15.3"
ID="opensuse-leap"
ID_LIKE="suse opensuse"
VERSION_ID="15.3"
PRETTY_NAME="openSUSE Leap 15.3"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:opensuse:leap:15.3"
BUG_REPORT_URL="https://bugs.opensuse.org"
HOME_URL="https://www.opensuse.org/"

STEPS TO REPRODUCE

- name: 'Create test file.'
  ansible.builtin.lineinfile:
    create: true
    path: "/tmp/test.sql"
    regexp: '^'
    line: >-
      select current_database();
      select now();
      select version();
      select usename from pg_catalog.pg_user;

- name: "Query"
  community.postgresql.postgresql_script:
    login_db: "postgres"
    login_user: "postgres"
    login_password: "123"
    login_port: 5432
    path: "/tmp/test.sql"
  register: output

- name: "Print var."
  ansible.builtin.debug:
    msg: "{{ output }}"

EXPECTED RESULTS

ok: [postgres_origin] => {
    "msg": {
        "changed": true,
        "failed": false,
        "query": "select current_database();\nselect now();\nselect version();\nselect usename from pg_catalog.pg_user;\n",
        "query_result": [
            {
                "current_database": "postgres",
                "rowcount": 1,
                "statusmessage": "SELECT 1"
            },
            {
                "now": "2024-10-22T14:22:19.337865+00:00"
                "rowcount": 1,
                "statusmessage": "SELECT 1"
            },
            {
                "version": "PostgreSQL 14.13 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit"
                "rowcount": 1,
                "statusmessage": "SELECT 1"
            },
            {
                "usename": "postgres",
                "usename": "reader"
                "rowcount": 2,
                "statusmessage": "SELECT 1"
            }
        ]
    }
}

ACTUAL RESULTS

ok: [postgres_origin] => {
    "msg": {
        "changed": true,
        "failed": false,
        "query": "select current_database();\nselect now();\nselect version();\nselect usename from pg_catalog.pg_user;\n",
        "query_result": [
            {
                "usename": "postgres"
                "usename": "reader"
            }
        ],
        "rowcount": 2,
        "statusmessage": "SELECT 1"
    }
}

[Andersson007]

@felipenazario thanks for opening the issue. The concerns raised in #312 are still valid. In particular, if in the postgresql_query module you can pass your queries as a list, so there's no problem with determining when one query starts and ends: it's just an element of the list and the module executes them one by one and appends the result. With the postgresql_script we should also create a list of queries first to be able to append the result of each query and return it, however:

1. the problem is that how to do it. you can't just use ";" because it can appear as a part of strings passed in the query like "SELECT blah FROM test_table WHERE blah = 'my text ; ' AND blah != ' ; another text containing semicolons' ... ; SELECT .."
2. even if there's a sophisticated solution for this issue, it'd make the logic and the code more complicated and error-prone.

So if the getting a result of each query is important for a user, I would suggest using postgresql_query instead. If the user wants to get queries from a file, I think they can use the lookup plugin to fetch it and then other plugins to transform it to the yml list and pass it to the _query module

[felipenazario]

Hi @Andersson007,

Nice to talk to you again, the last the we talked, I said...

As you already figure out, we do not split multiple queries in the same position. At least for now, when somebody send a Pull Request providing a special regex to find all query delimiters in all scenarios, it would be merged.

Below there is something to try the correct split.

import re

sql = "SELECT * FROM users; INSERT INTO users (name) VALUES ('John; Doe'); DELETE FROM users WHERE id = 1;INSERT INTO text VALUES ('my text ; blah ; blah'); SELECT 1"
statements = re.split(r';(?=(?:[^"\'"]|"[^"]*"|\'[^\']*\')*$)', sql)

# Remove empty strings and strip spaces
statements = [s.strip() for s in statements if s.strip()]

for statement in statements:
    print(statement)

Let me know what do you think of it. Meanwhile, I will be looking for what you suggested.

if the user wants to get queries from a file, I think they can use the lookup plugin to fetch it and then other plugins to transform it to the yml list and pass it to the _query module