community / community.general / 1.3.14 / module / postgresql_query Run PostgreSQL queries Authors: Felix Archambault (@archf), Andrew Klychkov (@Andersson007), Will Rouesnel (@wrouesnel)community.general.postgresql_query (1.3.14) — module
Install with ansible-galaxy collection install community.general:==1.3.14
collections: - name: community.general version: 1.3.14
Runs arbitrary PostgreSQL queries.
Can run queries from SQL script files.
Does not run against backup files. Use M(community.general.postgresql_db) with I(state=restore) to run queries on files made by pg_dump/pg_dumpall utilities.
- name: Simple select query to acme db community.general.postgresql_query: db: acme query: SELECT version()
- name: Select query to db acme with positional arguments and non-default credentials community.general.postgresql_query: 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.general.postgresql_query: 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.general.postgresql_query: db: test_db query: INSERT INTO test_table (id, story) VALUES (2, 'my_long_story')
- name: Run queries from SQL script using UTF-8 client encoding for session community.general.postgresql_query: db: test_db path_to_script: /var/lib/pgsql/test.sql positional_args: - 1 encoding: UTF-8
- name: Example of using autocommit parameter community.general.postgresql_query: db: test_db query: VACUUM autocommit: yes
- 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.general.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.general.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.general.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 }}'
db: aliases: - login_db description: - Name of database to connect to and run queries against. type: str port: aliases: - login_port default: 5432 description: - Database port to connect to. type: int query: description: - SQL query to run. Variables can be escaped with psycopg2 syntax U(http://initd.org/psycopg/docs/usage.html). type: str ca_cert: aliases: - ssl_rootcert description: - Specifies the name of a file containing SSL certificate authority (CA) certificate(s). - If the file exists, the server's certificate will be verified to be signed by one of these authorities. type: str 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 version_added_collection: community.general ssl_mode: choices: - allow - disable - prefer - require - verify-ca - verify-full default: prefer description: - Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server. - See U(https://www.postgresql.org/docs/current/static/libpq-ssl.html) for more information on the modes. - Default of C(prefer) matches libpq default. type: str autocommit: default: false 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 login_host: description: - Host running the database. type: str login_user: default: postgres description: - The username used to authenticate with. type: str 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 search_path: description: - List of schema names to look in. elements: str type: list version_added: 1.0.0 version_added_collection: community.general trust_input: default: true description: - If C(no), check whether a value of I(session_role) is potentially dangerous. - It makes sense to use C(no) only when SQL injections via I(session_role) are possible. type: bool version_added: 0.2.0 version_added_collection: community.general 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_password: description: - The password used to authenticate with. type: str path_to_script: description: - Path to a SQL script on the target machine. - If the script contains several queries, they must be semicolon-separated. - Mutually exclusive with I(query). type: path 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). elements: raw type: list login_unix_socket: description: - Path to a Unix domain socket for local connections. type: str
query: description: - Executed query. - When reading several queries from a file, it contains only the last one. returned: always sample: SELECT * FROM bar type: str query_all_results: description: - List containing results of all queries executed (one sublist for every query). Useful when reading several queries from a file. elements: list returned: always sample: - - Column: Value1 - Column: Value2 - - Column: Value1 - Column: Value2 type: list query_list: description: - List of executed queries. Useful when reading several queries from a file. elements: str returned: always sample: - SELECT * FROM foo - SELECT * FROM bar type: list 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. elements: dict returned: always sample: - Column: Value1 - Column: Value2 type: list 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 sample: 5 type: int 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: always sample: INSERT 0 1 type: str