1- name: postgresql_query - drop test table if exists 2 become_user: '{{ pg_user }}' 3 become: true 4 shell: psql postgres -U "{{ pg_user }}" -t -c "DROP TABLE IF EXISTS test_table;" 5 ignore_errors: true 6 7- name: postgresql_query - create test table called test_table 8 become_user: '{{ pg_user }}' 9 become: true 10 shell: psql postgres -U "{{ pg_user }}" -t -c "CREATE TABLE test_table (id int, story text);" 11 ignore_errors: true 12 13- name: postgresql_query - insert some data into test_table 14 become_user: '{{ pg_user }}' 15 become: true 16 shell: psql postgres -U "{{ pg_user }}" -t -c "INSERT INTO test_table (id, story) VALUES (1, 'first'), (2, 'second'), (3, 'third');" 17 ignore_errors: true 18 19- name: Copy script files 20 become: yes 21 copy: 22 src: '{{ item }}' 23 dest: '~{{ pg_user }}/{{ item }}' 24 owner: '{{ pg_user }}' 25 force: yes 26 loop: 27 - test0.sql 28 - test1.sql 29 register: sql_file_created 30 ignore_errors: yes 31 32- name: postgresql_query - analyze test_table 33 become_user: '{{ pg_user }}' 34 become: true 35 postgresql_query: 36 login_user: '{{ pg_user }}' 37 db: postgres 38 query: ANALYZE test_table 39 register: result 40 ignore_errors: true 41 42- assert: 43 that: 44 - result is changed 45 - result.query == 'ANALYZE test_table' 46 - result.query_list == ['ANALYZE test_table'] 47 - result.rowcount == 0 48 - result.statusmessage == 'ANALYZE' 49 - result.query_result == {} 50 - result.query_all_results == [{}] 51 52- name: postgresql_query - run queries from SQL script 53 become_user: '{{ pg_user }}' 54 become: true 55 postgresql_query: 56 login_user: '{{ pg_user }}' 57 db: postgres 58 path_to_script: ~{{ pg_user }}/test0.sql 59 positional_args: 60 - 1 61 encoding: UTF-8 62 register: result 63 ignore_errors: true 64 when: sql_file_created 65 66- assert: 67 that: 68 - result is not changed 69 - result.query == "\n\nSELECT story FROM test_table\n WHERE id = 1 OR story = 'Данные'" 70 - result.query_result[0].story == 'first' 71 - result.query_all_results[0][0].version is search('PostgreSQL') 72 - result.query_all_results[1][0].story == 'first' 73 - result.rowcount == 2 74 - result.statusmessage == 'SELECT 1' or result.statusmessage == 'SELECT' 75 when: sql_file_created 76 77- name: postgresql_query - simple select query to test_table 78 become_user: '{{ pg_user }}' 79 become: true 80 postgresql_query: 81 login_user: '{{ pg_user }}' 82 db: postgres 83 query: SELECT * FROM test_table 84 register: result 85 ignore_errors: true 86 87- assert: 88 that: 89 - result is not changed 90 - result.query == 'SELECT * FROM test_table' 91 - result.rowcount == 3 92 - result.statusmessage == 'SELECT 3' or result.statusmessage == 'SELECT' 93 - result.query_result[0].id == 1 94 - result.query_result[1].id == 2 95 - result.query_result[2].id == 3 96 - result.query_result[0].story == 'first' 97 - result.query_result[1].story == 'second' 98 - result.query_result[2].story == 'third' 99 100- name: postgresql_query - select query with named args 101 become_user: '{{ pg_user }}' 102 become: true 103 postgresql_query: 104 login_user: '{{ pg_user }}' 105 db: postgres 106 query: SELECT id FROM test_table WHERE id = %(id_val)s AND story = %(story_val)s 107 named_args: 108 id_val: 1 109 story_val: first 110 register: result 111 ignore_errors: true 112 113- assert: 114 that: 115 - result is not changed 116 - result.query == "SELECT id FROM test_table WHERE id = 1 AND story = 'first'" or result.query == "SELECT id FROM test_table WHERE id = 1 AND story = E'first'" 117 - result.rowcount == 1 118 - result.statusmessage == 'SELECT 1' or result.statusmessage == 'SELECT' 119 - result.query_result[0].id == 1 120 121- name: postgresql_query - select query with positional arguments 122 become_user: '{{ pg_user }}' 123 become: true 124 postgresql_query: 125 login_user: '{{ pg_user }}' 126 db: postgres 127 query: SELECT story FROM test_table WHERE id = %s AND story = %s 128 positional_args: 129 - 2 130 - second 131 register: result 132 ignore_errors: true 133 134- assert: 135 that: 136 - result is not changed 137 - result.query == "SELECT story FROM test_table WHERE id = 2 AND story = 'second'" or result.query == "SELECT story FROM test_table WHERE id = 2 AND story = E'second'" 138 - result.rowcount == 1 139 - result.statusmessage == 'SELECT 1' or result.statusmessage == 'SELECT' 140 - result.query_result[0].story == 'second' 141 142- name: postgresql_query - simple update query 143 become_user: '{{ pg_user }}' 144 become: true 145 postgresql_query: 146 login_user: '{{ pg_user }}' 147 db: postgres 148 query: UPDATE test_table SET story = 'new' WHERE id = 3 149 register: result 150 ignore_errors: true 151 152- assert: 153 that: 154 - result is changed 155 - result.query == "UPDATE test_table SET story = 'new' WHERE id = 3" 156 - result.rowcount == 1 157 - result.statusmessage == 'UPDATE 1' 158 - result.query_result == {} 159 160- name: check the previous update 161 become_user: '{{ pg_user }}' 162 become: true 163 postgresql_query: 164 login_user: '{{ pg_user }}' 165 db: postgres 166 query: SELECT * FROM test_table WHERE story = 'new' AND id = 3 167 register: result 168 169- assert: 170 that: 171 - result.rowcount == 1 172 173- name: postgresql_query - simple update query in check_mode 174 become_user: '{{ pg_user }}' 175 become: true 176 postgresql_query: 177 login_user: '{{ pg_user }}' 178 db: postgres 179 query: UPDATE test_table SET story = 'CHECK_MODE' WHERE id = 3 180 register: result 181 check_mode: true 182 183- assert: 184 that: 185 - result is changed 186 - result.query == "UPDATE test_table SET story = 'CHECK_MODE' WHERE id = 3" 187 - result.rowcount == 1 188 - result.statusmessage == 'UPDATE 1' 189 - result.query_result == {} 190 191- name: check the previous update that nothing has been changed 192 become_user: '{{ pg_user }}' 193 become: true 194 postgresql_query: 195 login_user: '{{ pg_user }}' 196 db: postgres 197 query: SELECT * FROM test_table WHERE story = 'CHECK_MODE' AND id = 3 198 register: result 199 200- assert: 201 that: 202 - result.rowcount == 0 203 204- name: postgresql_query - try to update not existing row 205 become_user: '{{ pg_user }}' 206 become: true 207 postgresql_query: 208 login_user: '{{ pg_user }}' 209 db: postgres 210 query: UPDATE test_table SET story = 'new' WHERE id = 100 211 register: result 212 ignore_errors: true 213 214- assert: 215 that: 216 - result is not changed 217 - result.query == "UPDATE test_table SET story = 'new' WHERE id = 100" 218 - result.rowcount == 0 219 - result.statusmessage == 'UPDATE 0' 220 - result.query_result == {} 221 222- name: postgresql_query - insert query 223 become_user: '{{ pg_user }}' 224 become: true 225 postgresql_query: 226 login_user: '{{ pg_user }}' 227 db: postgres 228 query: INSERT INTO test_table (id, story) VALUES (%s, %s) 229 positional_args: 230 - 4 231 - fourth 232 register: result 233 ignore_errors: true 234 235- assert: 236 that: 237 - result is changed 238 - result.query == "INSERT INTO test_table (id, story) VALUES (4, 'fourth')" or result.query == "INSERT INTO test_table (id, story) VALUES (4, E'fourth')" 239 - result.rowcount == 1 240 - result.statusmessage == 'INSERT 0 1' 241 - result.query_result == {} 242 243- name: postgresql_query - truncate test_table 244 become_user: '{{ pg_user }}' 245 become: true 246 postgresql_query: 247 login_user: '{{ pg_user }}' 248 db: postgres 249 query: TRUNCATE test_table 250 register: result 251 ignore_errors: true 252 253- assert: 254 that: 255 - result is changed 256 - result.query == "TRUNCATE test_table" 257 - result.rowcount == 0 258 - result.statusmessage == 'TRUNCATE TABLE' 259 - result.query_result == {} 260 261- name: postgresql_query - alter test_table 262 become_user: '{{ pg_user }}' 263 become: true 264 postgresql_query: 265 login_user: '{{ pg_user }}' 266 db: postgres 267 query: ALTER TABLE test_table ADD COLUMN foo int 268 register: result 269 ignore_errors: true 270 271- assert: 272 that: 273 - result is changed 274 - result.query == "ALTER TABLE test_table ADD COLUMN foo int" 275 - result.rowcount == 0 276 - result.statusmessage == 'ALTER TABLE' 277 278- name: postgresql_query - vacuum without autocommit must fail 279 become_user: '{{ pg_user }}' 280 become: true 281 postgresql_query: 282 login_user: '{{ pg_user }}' 283 db: postgres 284 query: VACUUM 285 register: result 286 ignore_errors: true 287 288- assert: 289 that: 290 - result.failed == true 291 292- name: postgresql_query - autocommit in check_mode must fail 293 become_user: '{{ pg_user }}' 294 become: true 295 postgresql_query: 296 login_user: '{{ pg_user }}' 297 db: postgres 298 query: VACUUM 299 autocommit: true 300 check_mode: true 301 register: result 302 ignore_errors: true 303 304- assert: 305 that: 306 - result.failed == true 307 - result.msg == "Using autocommit is mutually exclusive with check_mode" 308 309- name: postgresql_query - vacuum with autocommit 310 become_user: '{{ pg_user }}' 311 become: true 312 postgresql_query: 313 login_user: '{{ pg_user }}' 314 db: postgres 315 query: VACUUM 316 autocommit: true 317 register: result 318 319- assert: 320 that: 321 - result is changed 322 - result.query == "VACUUM" 323 - result.rowcount == 0 324 - result.statusmessage == 'VACUUM' 325 - result.query_result == {} 326 327- name: postgresql_query - create test table for issue 59955 328 become_user: '{{ pg_user }}' 329 become: true 330 postgresql_table: 331 login_user: '{{ pg_user }}' 332 login_db: postgres 333 name: test_array_table 334 columns: 335 - arr_col int[] 336 when: postgres_version_resp.stdout is version('9.4', '>=') 337 338- set_fact: 339 my_list: 340 - 1 341 - 2 342 - 3 343 my_arr: '{1, 2, 3}' 344 when: postgres_version_resp.stdout is version('9.4', '>=') 345 346- name: postgresql_query - insert array into test table by positional args 347 become_user: '{{ pg_user }}' 348 become: true 349 postgresql_query: 350 login_user: '{{ pg_user }}' 351 login_db: postgres 352 query: INSERT INTO test_array_table (arr_col) VALUES (%s) 353 positional_args: 354 - '{{ my_list }}' 355 register: result 356 when: postgres_version_resp.stdout is version('9.4', '>=') 357 358- assert: 359 that: 360 - result is changed 361 - result.query == "INSERT INTO test_array_table (arr_col) VALUES ('{1, 2, 3}')" 362 when: postgres_version_resp.stdout is version('9.4', '>=') 363 364- name: postgresql_query - select array from test table by passing positional_args 365 become_user: '{{ pg_user }}' 366 become: true 367 postgresql_query: 368 login_user: '{{ pg_user }}' 369 login_db: postgres 370 query: SELECT * FROM test_array_table WHERE arr_col = %s 371 positional_args: 372 - '{{ my_list }}' 373 register: result 374 when: postgres_version_resp.stdout is version('9.4', '>=') 375 376- assert: 377 that: 378 - result is not changed 379 - result.query == "SELECT * FROM test_array_table WHERE arr_col = '{1, 2, 3}'" 380 - result.rowcount == 1 381 when: postgres_version_resp.stdout is version('9.4', '>=') 382 383- name: postgresql_query - select array from test table by passing named_args 384 become_user: '{{ pg_user }}' 385 become: true 386 postgresql_query: 387 login_user: '{{ pg_user }}' 388 login_db: postgres 389 query: SELECT * FROM test_array_table WHERE arr_col = %(arr_val)s 390 named_args: 391 arr_val: 392 - '{{ my_list }}' 393 register: result 394 when: postgres_version_resp.stdout is version('9.4', '>=') 395 396- assert: 397 that: 398 - result is not changed 399 - result.query == "SELECT * FROM test_array_table WHERE arr_col = '{1, 2, 3}'" 400 - result.rowcount == 1 401 when: postgres_version_resp.stdout is version('9.4', '>=') 402 403- name: postgresql_query - select array from test table by passing positional_args as a string 404 become_user: '{{ pg_user }}' 405 become: true 406 postgresql_query: 407 login_user: '{{ pg_user }}' 408 login_db: postgres 409 query: SELECT * FROM test_array_table WHERE arr_col = %s 410 positional_args: 411 - '{{ my_arr|string }}' 412 trust_input: yes 413 register: result 414 when: postgres_version_resp.stdout is version('9.4', '>=') 415 416- assert: 417 that: 418 - result is not changed 419 - result.query == "SELECT * FROM test_array_table WHERE arr_col = '{1, 2, 3}'" 420 - result.rowcount == 1 421 when: postgres_version_resp.stdout is version('9.4', '>=') 422 423- name: postgresql_query - test trust_input parameter 424 become_user: '{{ pg_user }}' 425 become: true 426 postgresql_query: 427 login_user: '{{ pg_user }}' 428 login_db: postgres 429 session_role: 'curious.anonymous"; SELECT * FROM information_schema.tables; --' 430 query: SELECT version() 431 trust_input: no 432 ignore_errors: yes 433 register: result 434 435- assert: 436 that: 437 - result is failed 438 - result.msg is search('is potentially dangerous') 439 440- name: postgresql_query - clean up 441 become_user: '{{ pg_user }}' 442 become: true 443 postgresql_table: 444 login_user: '{{ pg_user }}' 445 login_db: postgres 446 name: test_array_table 447 state: absent 448 when: postgres_version_resp.stdout is version('9.4', '>=') 449 450############################# 451# Check search_path parameter 452 453- name: postgresql_set - create test schemas 454 become_user: '{{ pg_user }}' 455 become: true 456 postgresql_schema: 457 login_user: '{{ pg_user }}' 458 login_db: postgres 459 name: '{{ item }}' 460 loop: 461 - query_test1 462 - query_test2 463 464- name: postgresql_set - create test tables 465 become_user: '{{ pg_user }}' 466 become: true 467 postgresql_table: 468 login_user: '{{ pg_user }}' 469 login_db: postgres 470 name: '{{ item }}' 471 columns: 472 - id int 473 loop: 474 - 'query_test1.test1' 475 - 'query_test2.test2' 476 477- name: postgresql_query - insert data 478 become_user: '{{ pg_user }}' 479 become: true 480 postgresql_query: 481 login_user: '{{ pg_user }}' 482 login_db: postgres 483 query: 'INSERT INTO {{ item }} (id) VALUES (1)' 484 search_path: 485 - query_test1 486 - query_test2 487 loop: 488 - test1 489 - test2 490 491- name: postgresql_query - get data 492 become_user: '{{ pg_user }}' 493 become: true 494 postgresql_query: 495 login_user: '{{ pg_user }}' 496 login_db: postgres 497 query: 'SELECT id FROM test1' 498 search_path: 499 - query_test1 500 - query_test2 501 register: result 502 503- assert: 504 that: 505 - result.rowcount == 1 506 507- name: postgresql_query - get data, must fail 508 become_user: '{{ pg_user }}' 509 become: true 510 postgresql_query: 511 login_user: '{{ pg_user }}' 512 login_db: postgres 513 query: 'SELECT id FROM test1' 514 register: result 515 ignore_errors: yes 516 517- assert: 518 that: 519 - result is failed 520 521# Tests for the as_single_query option 522- name: Run queries from SQL script as a single query 523 become_user: '{{ pg_user }}' 524 become: true 525 postgresql_query: 526 login_user: '{{ pg_user }}' 527 db: postgres 528 path_to_script: ~{{ pg_user }}/test1.sql 529 positional_args: 530 - 1 531 encoding: UTF-8 532 as_single_query: yes 533 register: result 534 535- name: > 536 Must pass. Not changed because we can only 537 check statusmessage of the last query 538 assert: 539 that: 540 - result is not changed 541 - result.statusmessage == 'SELECT 1' or result.statusmessage == 'SELECT' 542 - result.query_list[0] == "CREATE FUNCTION add(integer, integer) RETURNS integer\n AS 'select $1 + $2;'\n LANGUAGE SQL\n IMMUTABLE\n RETURNS NULL ON NULL INPUT;\n\nSELECT story FROM test_table\n WHERE id = %s OR story = 'Данные';\n\nSELECT version();\n" 543 544############################################################################# 545# Issue https://github.com/ansible-collections/community.postgresql/issues/45 546- name: Create table containing a decimal value 547 become_user: '{{ pg_user }}' 548 become: true 549 postgresql_query: 550 login_user: '{{ pg_user }}' 551 db: postgres 552 query: CREATE TABLE blabla (id int, num decimal) 553 554- name: Insert data 555 become_user: '{{ pg_user }}' 556 become: true 557 postgresql_query: 558 login_user: '{{ pg_user }}' 559 db: postgres 560 query: INSERT INTO blabla (id, num) VALUES (1, 1::decimal) 561 562- name: Get data 563 become_user: '{{ pg_user }}' 564 become: true 565 postgresql_query: 566 login_user: '{{ pg_user }}' 567 db: postgres 568 query: SELECT * FROM blabla 569 register: result 570 571- assert: 572 that: 573 - result.rowcount == 1 574 575############################################################################# 576# Issue https://github.com/ansible-collections/community.postgresql/issues/47 577- name: Get datetime.timedelta value 578 become_user: '{{ pg_user }}' 579 become: true 580 postgresql_query: 581 login_user: '{{ pg_user }}' 582 db: postgres 583 query: "SELECT EXTRACT(epoch from make_interval(secs => 3))" 584 register: result 585 when: postgres_version_resp.stdout is version('10', '>=') 586 587- assert: 588 that: 589 - result.rowcount == 1 590 - result.query_result[0]["date_part"] == 3 591 when: postgres_version_resp.stdout is version('10', '>=') 592 593- name: Get interval value 594 become_user: '{{ pg_user }}' 595 become: true 596 postgresql_query: 597 login_user: '{{ pg_user }}' 598 db: postgres 599 query: "SELECT make_interval(secs => 3)" 600 register: result 601 when: postgres_version_resp.stdout is version('10', '>=') 602 603- assert: 604 that: 605 - result.rowcount == 1 606 - result.query_result[0]["make_interval"] == "0:00:03" 607 when: postgres_version_resp.stdout is version('10', '>=') 608