
    Vhi<                        d dl mZmZmZ eZdZdZdZd dl	Z	d dl
Z
d dlmZ d dlmZ d dlmZ d d	lmZ d d
lmZmZmZmZmZmZmZmZmZmZmZ d dlm Z  ere e d      k  rd dl!m"Z# nerd dl$m"Z# d Z%d Z&d Z'e(dk(  r e'        yy)    )absolute_importdivisionprint_functionae
  
---
module: postgresql_query
short_description: Run PostgreSQL queries
description:
- Runs arbitrary PostgreSQL queries.
options:
  query:
    description:
    - SQL query string or list of queries to run. Variables can be escaped with psycopg syntax
      U(https://www.psycopg.org/psycopg3/docs/basic/params.html).
    type: raw
  positional_args:
    description:
    - List of values to be passed as positional arguments to the query.
      When the value is a list, it will be converted to PostgreSQL array.
    - Mutually exclusive with I(named_args).
    type: list
    elements: raw
  named_args:
    description:
    - Dictionary of key-value arguments to pass to the query.
      When the value is a list, it will be converted to PostgreSQL array.
    - Mutually exclusive with I(positional_args).
    type: dict
  session_role:
    description:
    - Switch to session_role after connecting. The specified session_role must
      be a role that the current login_user is a member of.
    - Permissions checking for SQL commands is carried out as though
      the session_role were the one that had logged in originally.
    type: str
  login_db:
    description:
    - Name of database to connect to and run queries against.
    - The V(db) alias is deprecated and will be removed in version 5.0.0.
    type: str
    aliases:
    - db
  autocommit:
    description:
    - Execute in autocommit mode when the query can't be run inside a transaction block
      (e.g., VACUUM).
    - Mutually exclusive with I(check_mode).
    type: bool
    default: false
  encoding:
    description:
    - Set the client encoding for the current session (e.g. C(UTF-8)).
    - The default is the encoding defined by the database.
    type: str
    version_added: '0.2.0'
  trust_input:
    description:
    - If C(false), check whether a value of I(session_role) is potentially dangerous.
    - It makes sense to use C(false) only when SQL injections via I(session_role) are possible.
    type: bool
    default: true
    version_added: '0.2.0'
  search_path:
    description:
    - List of schema names to look in.
    type: list
    elements: str
    version_added: '1.0.0'
seealso:
- module: community.postgresql.postgresql_script
- module: community.postgresql.postgresql_db
- name: PostgreSQL Schema reference
  description: Complete reference of the PostgreSQL schema documentation.
  link: https://www.postgresql.org/docs/current/ddl-schemas.html

attributes:
  check_mode:
    support: full

author:
- Felix Archambault (@archf)
- Andrew Klychkov (@Andersson007)
- Will Rouesnel (@wrouesnel)

extends_documentation_fragment:
- community.postgresql.postgres
a  
- name: Simple select query to acme db
  community.postgresql.postgresql_query:
    login_db: acme
    query: SELECT version()

# The result of each query will be stored in query_all_results return value
- name: Run several queries against acme db
  community.postgresql.postgresql_query:
    login_db: acme
    query:
    - SELECT version()
    - SELECT id FROM accounts

- name: Select query to db acme with positional arguments and non-default credentials
  community.postgresql.postgresql_query:
    login_db: acme
    login_user: django
    login_password: mysecretpass
    query: SELECT * FROM acme WHERE id = %s AND story = %s
    positional_args:
    - 1
    - test

- name: Select query to test_db with named_args
  community.postgresql.postgresql_query:
    login_db: test_db
    query: SELECT * FROM test WHERE id = %(id_val)s AND story = %(story_val)s
    named_args:
      id_val: 1
      story_val: test

- name: Insert query to test_table in db test_db
  community.postgresql.postgresql_query:
    login_db: test_db
    query: INSERT INTO test_table (id, story) VALUES (2, 'my_long_story')

- name: Use connect_params to add any additional connection parameters that libpg supports
  community.postgresql.postgresql_query:
    connect_params:
      target_session_attrs: read-write
      connect_timeout: 10
    login_host: "host1,host2"
    login_user: "test"
    login_password: "test1234"
    login_db: "test"
    query: 'insert into test (test) values (now())'

- name: Example of using autocommit parameter
  community.postgresql.postgresql_query:
    login_db: test_db
    query: VACUUM
    autocommit: true

- name: >
    Insert data to the column of array type using positional_args.
    Note that we use quotes here, the same as for passing JSON, etc.
  community.postgresql.postgresql_query:
    query: INSERT INTO test_table (array_column) VALUES (%s)
    positional_args:
    - '{1,2,3}'

# Pass list and string vars as positional_args
- name: Set vars
  ansible.builtin.set_fact:
    my_list:
    - 1
    - 2
    - 3
    my_arr: '{1, 2, 3}'

- name: Select from test table by passing positional_args as arrays
  community.postgresql.postgresql_query:
    query: SELECT * FROM test_array_table WHERE arr_col1 = %s AND arr_col2 = %s
    positional_args:
    - '{{ my_list }}'
    - '{{ my_arr|string }}'

# Select from test table looking into app1 schema first, then,
# if the schema doesn't exist or the table hasn't been found there,
# try to find it in the schema public
- name: Select from test using search_path
  community.postgresql.postgresql_query:
    query: SELECT * FROM test_array_table
    search_path:
    - app1
    - public

# If you use a variable in positional_args / named_args that can
# be undefined and you wish to set it as NULL, the constructions like
# "{{ my_var if (my_var is defined) else none | default(none) }}"
# will not work as expected substituting an empty string instead of NULL.
# If possible, we suggest to use Ansible's DEFAULT_JINJA2_NATIVE configuration
# (https://docs.ansible.com/ansible/latest/reference_appendices/config.html#default-jinja2-native).
# Enabling it fixes this problem. If you cannot enable it, the following workaround
# can be used.
# You should precheck such a value and define it as NULL when undefined.
# For example:
- name: When undefined, set to NULL
  set_fact:
    my_var: NULL
  when: my_var is undefined

# Then:
- name: Insert a value using positional arguments
  community.postgresql.postgresql_query:
    query: INSERT INTO test_table (col1) VALUES (%s)
    positional_args:
    - '{{ my_var }}'
a  
query:
    description:
    - Executed query.
    - When reading several queries from a file, it contains only the last one.
    returned: success
    type: str
    sample: 'SELECT * FROM bar'
statusmessage:
    description:
    - Attribute containing the message returned by the command.
    - When reading several queries from a file, it contains a message of the last one.
    returned: success
    type: str
    sample: 'INSERT 0 1'
query_result:
    description:
    - List of dictionaries in column:value form representing returned rows.
    - When running queries from a file, returns result of the last query.
    returned: success
    type: list
    elements: dict
    sample: [{"Column": "Value1"},{"Column": "Value2"}]
query_list:
    description:
    - List of executed queries.
    returned: success
    type: list
    elements: str
    sample: ['SELECT * FROM foo', 'SELECT * FROM bar']
query_all_results:
    description:
    - List containing results of all queries executed (one sublist for every query).
    returned: success
    type: list
    elements: list
    sample: [[{"Column": "Value1"},{"Column": "Value2"}], [{"Column": "Value1"},{"Column": "Value2"}]]
rowcount:
    description:
    - Number of produced or affected rows.
    - When using a script with multiple queries,
      it contains a total number of produced or affected rows.
    returned: changed
    type: int
    sample: 5
execution_time_ms:
    description:
    - A list containing execution time per query in milliseconds.
    - The measurements are done right before and after passing
      the query to the driver for execution.
    returned: success
    type: list
    sample: [7104]
    version_added: '3.10.0'
N)	to_native)AnsibleModule)	iteritems)check_input)HAS_PSYCOPGPSYCOPG_VERSIONTYPES_NEED_TO_CONVERTconnect_to_dbconvert_elements_to_pg_arraysconvert_to_supportedensure_required_libsget_conn_paramspg_cursor_argspostgres_common_argument_specset_search_path)LooseVersionz3.0)ProgrammingErrorc                     t        j                         }| j                  ||       t        t        j                         |z
  dz  d      }| |fS )Ni     )timeperf_counterexecuteround)cursorqueryargs
start_timeexec_time_mss        y/home/dcms/DCMS/lib/python3.12/site-packages/ansible_collections/community/postgresql/plugins/modules/postgresql_query.pyexecute_and_return_timer#   +  sL     ""$J
NN5$ $++-
:dBAFL<    c                     | D ]  }|dvs y y)N) 
 	FT )stringcs     r"   insane_queryr-   7  s"     )) r$   c                  	   t               } | j                  t        d      t        ddgddddg      t        d	d
      t        d      t        d      t        dd      t        d      t        dd      t        d	d
      	       t        | dd      }|j                  d   }|j                  d   }|j                  d   }|j                  d   }|j                  d   }|j                  d   }|j                  d   }|j                  d   }	|r(t        |t        t        f      s|j                  d       |st        ||       |r|j                  r|j                  d       g }
t        |t              r|
j                  |       n|}
t        |       t        ||j                        }|||d<   t        |||      \  }} |j                  d3i t         }|	r;t#        |d d!j%                  |	D cg c]  }|j'                  d"       c}      z         |r|}n|r|}nd }|rt)        |      }d}g }g }d#}d$}|
D ]X  }	 |j+                  ||      }t-        |||      \  }}|j                  |       |j.                  }|j0                  d#kD  r||j0                  z  }g }	 |j3                         D ]P  }t        |      }t5        |      D ]$  \  }}t        |t6              st9        |      ||<   & |j                  |       R 	 |g k(  ri }|j                  |       d'|vrtd(|vrptA        jB                  tA        jD                  d)      |      rE|jG                         }tI        |      d*k(  r|d+   d,k7  r!d}ntI        |      d+k(  r|d-   d,k7  rd}nd}nd}[ |j                  r|jK                          n|s|jO                          t        ||
||||2      }|jM                          |jM                           |jP                  d3i | y c c}w # t:        $ r}t=        |      d%k(  ri }Y d }~,d }~wt>        $ r)}|j                  d&t=        |      z         Y d }~\d }~ww xY w# t>        $ rd}|s|jK                          |jM                          |jM                          |j                  d.|d/|d0t=        |      d1|
       Y d }~d }~ww xY w)4Nraw)typestrdbz5.0.0zcommunity.postgresql)nameversioncollection_name)r0   aliasesdeprecated_aliaseslist)r0   elementsdictboolF)r0   defaultT)	r   login_dbpositional_args
named_argssession_role
autocommitencodingtrust_inputsearch_path))r>   r?   )argument_specmutually_exclusivesupports_check_moder   r>   r?   rA   rB   r@   rC   rD   z-query argument must be of type string or list)msgz6Using autocommit is mutually exclusive with check_modeclient_encoding)rA   z%s,r&   r   r(   zno results to fetchz!Cannot fetch rows from cursor: %sSELECTSHOWz(UPDATE|INSERT|DELETE)      0   zCannot execute SQL 'z' z: z, query list: )changedr   
query_liststatusmessagequery_resultquery_all_resultsrowcountexecution_time_msr*   ))r   updater:   r   params
isinstancer1   r8   	fail_jsonr	   
check_modeappendr   r   r   r   r   r   joinstripr   mogrifyr#   rS   rV   fetchallr   r   r   PsycopgProgrammingErrorr   	Exceptionresearchcompilesplitlenrollbackclosecommit	exit_json)rE   moduler   r>   r?   rA   rB   r@   rC   rD   rR   conn_paramsdb_connectiondummyr   xr   rQ   rU   rW   rV   rS   current_query_txtr!   rT   rowkeyvaleskws                                  r"   mainry   ?  s   13M54&"#9F 
 &59V$u%VU35!fd3fu5  $ #? F MM'"Emm$56O|,J|,J}}Z(H==0L--.K--.KZT{3LMFL)f''UVJ%% 
  !&&--8K)1%&(TM5!]!!3N3Fsxx{0S!0S'T TU 	 ,T2 GHM  =}<	} &ud ;#:65$#O FL$$\2"00M"FOO+LY!??, -C s)C&/n A
c%c+@A';C'@CHA !'',-& r!!$$\2},}1L99RZZ(ABMR%++-A1v{Q43;&*GQ1Q43;&*G #' #Gk=}~    "	#!++	
B LLNFrW 1Td + &Q<#88#%L Y  %H9UV<%W XXY2  	}&&(LLN!UZ\`bklmbnpz!{||	}sc   6PAQ-%?P%#PBQ-	Q*P5/Q-5Q*Q%Q-%Q**Q--	S6ASS__main__))
__future__r   r   r   r0   __metaclass__DOCUMENTATIONEXAMPLESRETURNrd   r   ansible.module_utils._textr   ansible.module_utils.basicr   ansible.module_utils.sixr   Fansible_collections.community.postgresql.plugins.module_utils.databaser	   Fansible_collections.community.postgresql.plugins.module_utils.postgresr
   r   r   r   r   r   r   r   r   r   r   Eansible_collections.community.postgresql.plugins.module_utils.versionr   psycopg2r   rb   psycopgr#   r-   ry   __name__r*   r$   r"   <module>r      s    A @Sjm^6
p 
  0 4 .    ?\%%88DC	 hV zF r$   