community / community.general / 0.1.4 / module / postgresql_query Run PostgreSQL queries Authors: Felix Archambault (@archf), Andrew Klychkov (@Andersson007), Will Rouesnel (@wrouesnel)community.general.postgresql_query (0.1.4) — module
Install with ansible-galaxy collection install community.general:==0.1.4
collections: - name: community.general version: 0.1.4
Runs arbitrary PostgreSQL queries.
Can run queries from SQL script files.
Does not run against backup files. Use M(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 postgresql_query: db: acme query: SELECT version()
- name: Select query to db acme with positional arguments and non-default credentials 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 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 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 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 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. 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 set_fact: my_list: - 1 - 2 - 3 my_arr: '{1, 2, 3}'
- name: Select from test table by passing positional_args as arrays postgresql_query: query: SELECT * FROM test_array_table WHERE arr_col1 = %s AND arr_col2 = %s positional_args: - '{{ my_list }}' - '{{ my_arr|string }}'
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 trust_input: default: true description: - If C(no), check whether a value of I(session_role) is potentially dangerous. - It does make sense to use C(yes) 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 SQL script on the remote host. - Returns result of the last query in the script. - 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: Query that was tried to be executed. returned: always sample: SELECT * FROM bar type: str query_result: description: - List of dictionaries in column:value form representing returned rows. returned: changed sample: - Column: Value1 - Column: Value2 type: list rowcount: description: Number of affected rows. returned: changed sample: 5 type: int statusmessage: description: Attribute containing the message returned by the command. returned: always sample: INSERT 0 1 type: str