postgresql_query, postgresql_script: array argument conversion

[betanummeric]

The postgresql_query and postgresql_script modules use this code to transform a list in the arguments to an postgres array string: https://github.com/ansible-collections/community.postgresql/blob/2.3.2/plugins/module_utils/postgres.py#L387-L422

quoting issue

The current implementation doesn't work for a list of strings, because the single quotes from python become part of the array value:

- hosts: all
  gather_facts: no
  remote_user: postgres
  tasks:
    - name: create one rule to clear
      community.postgresql.postgresql_query:
        login_unix_socket: /tmp
        query: |
          select unnest(%(input_list)s::text[])
        named_args:
          input_list:
            - a
            - b 
      register: query_result
    - name: show result
      debug:
        msg: "{{ query_result }}"

results in

...
        "query": "select unnest('{''a'', ''b''}'::text[])\n",
        "query_list": [
            "select unnest(%(input_list)s::text[])\n"
        ],
        "query_result": [
            {
                "unnest": "'a'"
            },
            {
                "unnest": "'b'"
            }
        ],
        "rowcount": 2,
...

while I would expect something like

...
        "query": "select unnest('{"a","b"}'::text[])\n",
        "query_list": [
            "select unnest(%(input_list)s::text[])\n"
        ],
        "query_result": [
            {
                "unnest": "a"
            },
            {
                "unnest": "b"
            }
        ],
        "rowcount": 2,
...

because single and double quotes are not the same in postgres:

postgres=# select '{''a'',b}'::text[] = '{a,b}'::text[];
 ?column? 
----------
 f
(1 row)

Time: 0.538 ms
postgres=# select '{"a",b}'::text[] = '{a,b}'::text[];
 ?column? 
----------
 t
(1 row)

Time: 0.782 ms

ways to fix

psycopg2 knows how to convert python lists into postgres arrays: https://www.psycopg.org/docs/usage.html#adapt-list
I think we could just skip the conversion done in ansible and leave it to psycopg2.

If there is a reason to keep the conversion in ansible, I propose to change it from

def list_to_pg_array(elem):
    """Convert the passed list to PostgreSQL array
    represented as a string.
    Args:
        elem (list): List that needs to be converted.
    Returns:
        elem (str): String representation of PostgreSQL array.
    """
    elem = str(elem).strip('[]')
    elem = '{' + elem + '}'
    return elem

to

def list_to_pg_array(py_list):
  return '{' + ','.join([ '"' + str(item).replace('"', '\\"') + '"' for item in py_list ]) +  '}'

[Andersson007]

@betanummeric hello, thanks for reporting the issue!
I don't remember any reason to use the code instead of psycopg2 functionality (maybe who wrote it-probably me-just didn't know the better way). So let's try it?
Provided that it works and it's covered by CI, it'll be a good fix