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