Vh/ddlmZmZmZeZdZdZdZddl m Z ddl m Z ddl mZddlmZdd lmZmZmZmZmZmZmZmZmZmZmZdd lmZereed krdd lm Z!nerdd l"m Z!d Z#e$dk(re#yy))absolute_importdivisionprint_functiona0 --- module: postgresql_script short_description: Run PostgreSQL statements from a file description: - Runs arbitrary PostgreSQL statements from a file. - The module always reports that the state has changed. - Do NOT run the module against files generated by C(pg_dump), C(pg_dumpall), C(pg_restore) and other PostgreSQL utilities. - Use M(community.postgresql.postgresql_db) with I(state=restore) to run queries from files generated by C(pg_dump/pg_dumpall). version_added: '2.1.0' options: positional_args: description: - List of values to substitute variable placeholders within the file content. - 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 substitute variable placeholders within the file content. - When the value is a list, it will be converted to PostgreSQL array. - Mutually exclusive with I(positional_args). type: dict path: description: - Path to a SQL script on the target machine. - To upload dumps, the preferable way is to use the M(community.postgresql.postgresql_db) module with I(state=restore). type: path session_role: description: - Switch to C(session_role) after connecting. The specified role must be a role that the current C(login_user) is a member of. - Permissions checking for SQL commands is carried out as though the C(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 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 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 search_path: description: - Overrides the list of schemas to search for db objects in. type: list elements: str seealso: - module: community.postgresql.postgresql_db - module: community.postgresql.postgresql_query - 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: none author: - Douglas J Hunley (@hunleyd) - A. Hart (@jtelcontar) - Daniel Scharon (@DanScharon) - Andrew Klychkov (@Andersson007) extends_documentation_fragment: - community.postgresql.postgres aY # Assuming that the file contains # SELECT * FROM id_talbe WHERE id = %s, # '%s' will be substituted with 1 - name: Run query from SQL script using UTF-8 client encoding for session and positional args community.postgresql.postgresql_script: login_db: test_db path: /var/lib/pgsql/test.sql positional_args: - 1 encoding: UTF-8 # Assuming that the file contains # SELECT * FROM test WHERE id = %(id_val)s AND story = %(story_val)s, # %-values will be substituted with 1 and 'test' - name: Select query to test_db with named_args community.postgresql.postgresql_script: login_db: test_db path: /var/lib/pgsql/test.sql named_args: id_val: 1 story_val: test - block: # Assuming that the the file contains # SELECT * FROM test_array_table WHERE arr_col1 = %s AND arr_col2 = %s # 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: Passing positional_args as arrays community.postgresql.postgresql_script: path: /var/lib/pgsql/test.sql positional_args: - '{{ my_list }}' - '{{ my_arr|string }}' # Assuming that the the file contains # SELECT * FROM test_table, # look 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_script: path: /var/lib/pgsql/test.sql search_path: - app1 - public - block: # If you use a variable in positional_args/named_args that can # be undefined and you wish to set it as NULL, 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 using 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, assuming that the file contains # INSERT INTO test_table (col1) VALUES (%s) - name: Insert a value using positional arguments community.postgresql.postgresql_script: path: /var/lib/pgsql/test.sql positional_args: - '{{ my_var }}' a query: description: - Executed query. - When the C(positional_args) or C(named_args) options are used, the query contains all variables that were substituted inside the database connector. returned: success type: str sample: 'SELECT * FROM bar' statusmessage: description: - Attribute containing the message returned by the database connector after executing the script content. - When there are several statements in the script, returns a message related to the last statement. returned: success type: str sample: 'INSERT 0 1' query_result: description: - List of dictionaries in the column:value form representing returned rows. - When there are several statements in the script, returns result of the last statement. returned: success type: list elements: dict sample: [{"Column": "Value1"},{"Column": "Value2"}] rowcount: description: - Number of produced or affected rows. - When there are several statements in the script, returns a number of rows affected by the last statement. returned: changed type: int sample: 5 ) to_native) AnsibleModule) iteritems) check_input) HAS_PSYCOPGPSYCOPG_VERSIONTYPES_NEED_TO_CONVERT connect_to_dbconvert_elements_to_pg_arraysconvert_to_supportedensure_required_libsget_conn_paramspg_cursor_argspostgres_common_argument_specset_search_path) LooseVersion3.0)ProgrammingErrorc t}|jtdtddgddddgtd d td tdtdtd dtd d t|dd}|jd}|jd}|jd}|jd}|jd}|jd}|jd}|s t || t |d5} t| j} dddt|t||j} ||| d<t|| d\} }| jd*it}|r;t!|d d!j#|Dcgc]}|j%d"c}z|r|}n|r|}nd}|r t'|} |j) |}|j+| ||j.}|j0}g} |j3}t4t7d&k\r1|j9!|j3}|j9!|D]P}t|}t;|D]$\}}t=|t>stA|||<&|jC|R |gk(ri}td|||)}|j-| j-|jFd*i|y#1swYxYw#t$r,} |jd|dt| Yd} ~ d} ~ wwxYwcc}w#t$rO} |j-| j-|jd# d$|d%t| Yd} ~ d} ~ wwxYw#tD$r} t| d'k(ri}Yd} ~ d} ~ wt$r)} |jd(t| zYd} ~ >d} ~ wwxYw)+Npath)typestrdbz5.0.0zcommunity.postgresql)nameversioncollection_name)raliasesdeprecated_aliaseslistraw)relementsdictboolT)rdefault)rlogin_dbpositional_args named_args session_roleencoding trust_input search_path))r)r*F) argument_specmutually_exclusivesupports_check_moder)r*r,r+r-r.rbzCannot read file 'z' : )msgclient_encoding) autocommitz%s, zCannot execute SQL 'z' z: rzno results to fetchz!Cannot fetch rows from cursor: %s)changedquery statusmessage query_resultrowcount)$rupdater%rparamsr openrread Exception fail_jsonrrr cursorrrjoinstriprmogrifyexecutecloser:r<fetchallr rnextsetr isinstancer rappendPsycopgProgrammingError exit_json)r/modulerr)r*r,r+r-r.fscript_contente conn_params db_connectiondummyrDxargscurrent_query_txtr:r<r; result_setrowkeyvalkws z/home/dcms/DCMS/lib/python3.12/site-packages/ansible_collections/community/postgresql/plugins/modules/postgresql_script.pymainr`s13M v 54&"#9 F &59V$u%5!fd3fu5"#?!F == Dmm$56O|,J}}Z(H==0L-- .K-- .K FL)R $  1&qvvx0N 1 !&&--8K)1 %&(NM5 !] ! ! 3N 3Fsxx{0S!0S'T TU  ,T2f"NN>4@~t, ((MH LQ__& l51 1..".#__. ..". %Cs)C'n 9 cc#893C8CH 9    $ %&r #!  B LLNFrk 1 1 RT9Q<PQQR1T& f QUW`abWcdeef< # Q<0 0L Q@9Q<OPPQs LL +LM$MAN0+1N0#N0 LL M !!MM  N-AN((N-0 P9O PO??P__main__N)% __future__rrrr __metaclass__ DOCUMENTATIONEXAMPLESRETURNansible.module_utils._textransible.module_utils.basicransible.module_utils.sixrFansible_collections.community.postgresql.plugins.module_utils.databaser Fansible_collections.community.postgresql.plugins.module_utils.postgresr r r r rrrrrrrEansible_collections.community.postgresql.plugins.module_utils.versionrpsycopg2rrNpsycopgr`__name__r=r_rqsA@ X tL \$ L14.    ?\%%88DC{| zFrp