Description
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 elemto
def list_to_pg_array(py_list):
return '{' + ','.join([ '"' + str(item).replace('"', '\\"') + '"' for item in py_list ]) + '}'