2 -- Tests for psql features that aren't closely connected to any
3 -- specific server features
10 -- fail: invalid value for special variable
13 -- check handling of built-in boolean variable
14 \echo :ON_ERROR_ROLLBACK
15 \set ON_ERROR_ROLLBACK
16 \echo :ON_ERROR_ROLLBACK
17 \set ON_ERROR_ROLLBACK foo
18 \echo :ON_ERROR_ROLLBACK
19 \set ON_ERROR_ROLLBACK on
20 \echo :ON_ERROR_ROLLBACK
21 \unset ON_ERROR_ROLLBACK
22 \echo :ON_ERROR_ROLLBACK
26 SELECT 1 as one, 2 as two \g
28 SELECT 3 as three, 4 as four \gx
31 -- \gx should work in FETCH_COUNT mode too
34 SELECT 1 as one, 2 as two \g
36 SELECT 3 as three, 4 as four \gx
43 select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_
45 \echo :pref01_test01 :pref01_test02 :pref01_test03
47 -- should fail: bad variable name
48 select 10 as "bad name"
51 -- multiple backslash commands in one line
52 select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x
53 select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y
54 select 5 as x, 6 as y \gset pref01_ \\ \g \echo :pref01_x :pref01_y
55 select 7 as x, 8 as y \g \gset pref01_ \echo :pref01_x :pref01_y
57 -- NULL should unset the variable
59 select 1 as var1, NULL as var2, 3 as var3 \gset
60 \echo :var1 :var2 :var3
62 -- \gset requires just one tuple
63 select 10 as test01, 20 as test02 from generate_series(1,3) \gset
64 select 10 as test01, 20 as test02 from generate_series(1,0) \gset
66 -- \gset should work in FETCH_COUNT mode too
69 select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x
70 select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y
71 select 10 as test01, 20 as test02 from generate_series(1,3) \gset
72 select 10 as test01, 20 as test02 from generate_series(1,0) \gset
85 'foo'::varchar(4) as six,
89 -- should work with tuple-returning utilities, such as EXECUTE
90 PREPARE test AS SELECT 1 AS first, 2 AS second;
92 EXPLAIN EXECUTE test \gdesc
94 -- should fail cleanly - syntax error
97 -- check behavior with empty results
99 CREATE TABLE bububu(a int) \gdesc
101 -- subject command should not have executed
102 TABLE bububu; -- fail
104 -- query buffer should remain unchanged
105 SELECT 1 AS x, 'Hello', 2 AS y, true AS "dirty\name"
110 SELECT 3 AS x, 'Hello', 4 AS y, true AS "dirty\name" \gdesc \g
114 create temporary table gexec_test(a int, b text, c date, d float);
115 select format('create index on gexec_test(%I)', attname)
117 where attrelid = 'gexec_test'::regclass and attnum > 0
121 -- \gexec should work in FETCH_COUNT mode too
122 -- (though the fetch limit applies to the executed queries not the meta query)
125 select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)'
127 select 'drop table gexec_test', NULL
129 select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
134 -- show all pset options
137 -- test multi-line headers, wrapping, and newline indicators
138 -- in aligned, unaligned, and wrapped formats
139 prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab
141 c", array_to_string(array_agg(repeat('y',20-2*n)),E'\n') as "a
142 bc" from generate_series(1,10) as n(n) group by n>1 order by n>1;
144 \pset linestyle ascii
150 \pset format unaligned
158 \pset format unaligned
166 \pset format unaligned
177 \pset format unaligned
185 \pset format unaligned
193 \pset format unaligned
200 \pset linestyle old-ascii
206 \pset format unaligned
214 \pset format unaligned
222 \pset format unaligned
233 \pset format unaligned
241 \pset format unaligned
249 \pset format unaligned
258 -- test single-line header and data
259 prepare q as select repeat('x',2*n) as "0123456789abcdef", repeat('y',20-2*n) as "0123456789" from generate_series(1,10) as n;
261 \pset linestyle ascii
267 \pset format unaligned
275 \pset format unaligned
283 \pset format unaligned
294 \pset format unaligned
302 \pset format unaligned
310 \pset format unaligned
321 \pset format unaligned
329 \pset format unaligned
337 \pset format unaligned
344 \pset linestyle old-ascii
350 \pset format unaligned
358 \pset format unaligned
366 \pset format unaligned
376 \pset format unaligned
384 \pset format unaligned
392 \pset format unaligned
401 \pset linestyle ascii
404 -- support table for output-format tests (useful to create a footer)
406 create table psql_serial_tab (id serial);
408 -- test header/footer/tuples_only behavior in aligned/unaligned/wrapped cases
413 \d psql_serial_tab_id_seq
414 \pset tuples_only true
416 \pset tuples_only false
418 \d psql_serial_tab_id_seq
419 \pset tuples_only true
421 \pset tuples_only false
422 -- empty table is a special case for this format
423 select 1 where false;
425 \pset format unaligned
428 \d psql_serial_tab_id_seq
429 \pset tuples_only true
431 \pset tuples_only false
433 \d psql_serial_tab_id_seq
434 \pset tuples_only true
436 \pset tuples_only false
441 \d psql_serial_tab_id_seq
442 \pset tuples_only true
444 \pset tuples_only false
446 \d psql_serial_tab_id_seq
447 \pset tuples_only true
449 \pset tuples_only false
451 -- check conditional tableam display
453 -- Create a heap2 table am handler with heapam handler
454 CREATE ACCESS METHOD heap_psql TYPE TABLE HANDLER heap_tableam_handler;
455 CREATE TABLE tbl_heap_psql(f1 int, f2 char(100)) using heap_psql;
456 CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap;
459 \set HIDE_TABLEAM off
463 DROP TABLE tbl_heap, tbl_heap_psql;
464 DROP ACCESS METHOD heap_psql;
466 -- test numericlocale (as best we can without control of psql's locale)
470 \pset numericlocale true
472 select n, -n as m, n * 111 as x, '1e90'::float8 as f
473 from generate_series(0,3) n;
475 \pset numericlocale false
477 -- test asciidoc output format
479 \pset format asciidoc
483 \d psql_serial_tab_id_seq
484 \pset tuples_only true
486 \pset tuples_only false
488 \d psql_serial_tab_id_seq
489 \pset tuples_only true
491 \pset tuples_only false
494 select 'some|text' as "a|title", ' ' as "empty ", n as int
495 from generate_series(1,2) as n;
519 -- test csv output format
525 \d psql_serial_tab_id_seq
526 \pset tuples_only true
528 \pset tuples_only false
530 \d psql_serial_tab_id_seq
531 \pset tuples_only true
533 \pset tuples_only false
536 select 'some"text' as "a""title", E' <foo>\n<bar>' as "junk",
537 ' ' as "empty", n as int
538 from generate_series(1,2) as n;
550 select 'comma,comma' as comma, 'semi;semi' as semi;
551 \pset csv_fieldsep ';'
552 select 'comma,comma' as comma, 'semi;semi' as semi;
554 \pset csv_fieldsep '.'
555 select '\' as d1, '' as d2;
557 -- illegal csv separators
558 \pset csv_fieldsep ''
559 \pset csv_fieldsep '\0'
560 \pset csv_fieldsep '\n'
561 \pset csv_fieldsep '\r'
562 \pset csv_fieldsep '"'
563 \pset csv_fieldsep ',,'
565 \pset csv_fieldsep ','
567 -- test html output format
573 \d psql_serial_tab_id_seq
574 \pset tuples_only true
576 \pset tuples_only false
578 \d psql_serial_tab_id_seq
579 \pset tuples_only true
581 \pset tuples_only false
584 select 'some"text' as "a&title", E' <foo>\n<bar>' as "junk",
585 ' ' as "empty", n as int
586 from generate_series(1,2) as n;
595 \pset tableattr foobar
606 \pset tableattr foobar
612 -- test latex output format
618 \d psql_serial_tab_id_seq
619 \pset tuples_only true
621 \pset tuples_only false
623 \d psql_serial_tab_id_seq
624 \pset tuples_only true
626 \pset tuples_only false
629 select 'some\more_text' as "a$title", E' #<foo>%&^~|\n{bar}' as "junk",
630 ' ' as "empty", n as int
631 from generate_series(1,2) as n;
661 -- test latex-longtable output format
663 \pset format latex-longtable
667 \d psql_serial_tab_id_seq
668 \pset tuples_only true
670 \pset tuples_only false
672 \d psql_serial_tab_id_seq
673 \pset tuples_only true
675 \pset tuples_only false
678 select 'some\more_text' as "a$title", E' #<foo>%&^~|\n{bar}' as "junk",
679 ' ' as "empty", n as int
680 from generate_series(1,2) as n;
718 -- test troff-ms output format
720 \pset format troff-ms
724 \d psql_serial_tab_id_seq
725 \pset tuples_only true
727 \pset tuples_only false
729 \d psql_serial_tab_id_seq
730 \pset tuples_only true
732 \pset tuples_only false
735 select 'some\text' as "a\title", E' <foo>\n<bar>' as "junk",
736 ' ' as "empty", n as int
737 from generate_series(1,2) as n;
761 -- check ambiguous format requests
766 -- clean up after output format tests
768 drop table psql_serial_tab;
774 -- \echo and allied features
777 \echo -n without newline
778 \echo with -n newline
779 \echo '-n' with newline
784 \qecho this is a test
790 -- tests for \if ... \endif
800 -- at this point query buffer should still have last valid line
803 -- \if should work okay on part of a query
812 select \if false \\ (bogus \else \\ 42 \endif \\ forty_two;
814 -- test a large nested if using a variety of true-equivalents
821 \echo 'should not print #1-1'
824 \echo 'should not print #1-2'
827 \echo 'should not print #1-3'
830 \echo 'should not print #1-4'
833 -- test a variety of false-equivalents in an if/elif/else structure
835 \echo 'should not print #2-1'
837 \echo 'should not print #2-2'
839 \echo 'should not print #2-3'
841 \echo 'should not print #2-4'
846 -- test true-false elif after initial true branch
848 \echo 'should print #2-5'
850 \echo 'should not print #2-6'
852 \echo 'should not print #2-7'
854 \echo 'should not print #2-8'
857 -- test simple true-then-else
859 \echo 'first thing true'
861 \echo 'should not print #3-1'
864 -- test simple false-true-else
866 \echo 'should not print #4-1'
868 \echo 'second thing true'
870 \echo 'should not print #5-1'
873 -- invalid boolean expressions are false
874 \if invalid boolean expression
875 \echo 'will not print #6-1'
877 \echo 'will print anyway #6-2'
880 -- test un-matched endif
883 -- test un-matched else
886 -- test un-matched elif
889 -- test double-else error
895 -- test elif out-of-order
901 -- test if-endif matching in a false branch
904 \echo 'should not print #7-1'
906 \echo 'should not print #7-2'
908 \echo 'should not print #7-3'
910 \echo 'should print #7-4'
913 -- show that vars and backticks are not expanded when ignoring extra args
915 \echo :foo :'foo' :"foo"
916 \pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
918 -- show that vars and backticks are not expanded and commands are ignored
919 -- when in a false if-branch
920 \set try_to_quit '\\q'
923 \echo `nosuchcommand` :foo :'foo' :"foo"
924 \pset fieldsep | `nosuchcommand` :foo :'foo' :"foo"
927 \c arg1 arg2 arg3 arg4
930 \copy arg1 arg2 arg3 arg4 arg5 arg6
932 SELECT 1 as one, 2, 3 \crosstabview
937 \echo arg1 arg2 arg3 arg4 arg5
945 SELECT 1 AS one \gset
962 \set arg1 arg2 arg3 arg4 arg5 arg6 arg7
973 -- \else here is eaten as part of OT_FILEPIPE argument
974 \w |/no/such/file \else
975 -- \endif here is eaten as part of whole-line argument
979 \echo 'should print #8-1'
982 -- :{?...} defined variable test
985 \echo '#9-1 ok, variable i is defined'
987 \echo 'should not print #9-2'
990 \if :{?no_such_variable}
991 \echo 'should not print #10-1'
993 \echo '#10-2 ok, variable no_such_variable is not defined'
996 SELECT :{?i} AS i_is_defined;
998 SELECT NOT :{?no_such_var} AS no_such_var_is_not_defined;
1002 \set SHOW_CONTEXT never
1006 raise exception 'bar';
1009 \set SHOW_CONTEXT errors
1013 raise exception 'bar';
1016 \set SHOW_CONTEXT always
1020 raise exception 'bar';
1023 -- test printing and clearing the query buffer
1035 -- tests for special result variables
1037 -- working query, 2 rows selected
1038 SELECT 1 AS stuff UNION SELECT 2;
1039 \echo 'error:' :ERROR
1040 \echo 'error code:' :SQLSTATE
1041 \echo 'number of rows:' :ROW_COUNT
1045 \echo 'error:' :ERROR
1046 \echo 'error code:' :SQLSTATE
1047 \echo 'number of rows:' :ROW_COUNT
1048 \echo 'last error message:' :LAST_ERROR_MESSAGE
1049 \echo 'last error code:' :LAST_ERROR_SQLSTATE
1053 \echo 'error:' :ERROR
1054 \echo 'error code:' :SQLSTATE
1055 \echo 'number of rows:' :ROW_COUNT
1056 -- must have kept previous values
1057 \echo 'last error message:' :LAST_ERROR_MESSAGE
1058 \echo 'last error code:' :LAST_ERROR_SQLSTATE
1060 -- other query error
1061 DROP TABLE this_table_does_not_exist;
1062 \echo 'error:' :ERROR
1063 \echo 'error code:' :SQLSTATE
1064 \echo 'number of rows:' :ROW_COUNT
1065 \echo 'last error message:' :LAST_ERROR_MESSAGE
1066 \echo 'last error code:' :LAST_ERROR_SQLSTATE
1068 -- nondefault verbosity error settings (except verbose, which is too unstable)
1069 \set VERBOSITY terse
1071 \echo 'error:' :ERROR
1072 \echo 'error code:' :SQLSTATE
1073 \echo 'last error message:' :LAST_ERROR_MESSAGE
1075 \set VERBOSITY sqlstate
1077 \echo 'error:' :ERROR
1078 \echo 'error code:' :SQLSTATE
1079 \echo 'last error message:' :LAST_ERROR_MESSAGE
1081 \set VERBOSITY default
1084 SELECT 3 AS three, 4 AS four \gdesc
1085 \echo 'error:' :ERROR
1086 \echo 'error code:' :SQLSTATE
1087 \echo 'number of rows:' :ROW_COUNT
1089 -- \gdesc with an error
1091 \echo 'error:' :ERROR
1092 \echo 'error code:' :SQLSTATE
1093 \echo 'number of rows:' :ROW_COUNT
1094 \echo 'last error message:' :LAST_ERROR_MESSAGE
1095 \echo 'last error code:' :LAST_ERROR_SQLSTATE
1097 -- check row count for a cursor-fetched query
1099 select unique2 from tenk1 order by unique2 limit 19;
1100 \echo 'error:' :ERROR
1101 \echo 'error code:' :SQLSTATE
1102 \echo 'number of rows:' :ROW_COUNT
1104 -- cursor-fetched query with an error after the first group
1105 select 1/(15-unique2) from tenk1 order by unique2 limit 19;
1106 \echo 'error:' :ERROR
1107 \echo 'error code:' :SQLSTATE
1108 \echo 'number of rows:' :ROW_COUNT
1109 \echo 'last error message:' :LAST_ERROR_MESSAGE
1110 \echo 'last error code:' :LAST_ERROR_SQLSTATE
1114 create schema testpart;
1115 create role regress_partitioning_role;
1117 alter schema testpart owner to regress_partitioning_role;
1119 set role to regress_partitioning_role;
1121 -- run test inside own schema and hide other partitions
1122 set search_path to testpart;
1124 create table testtable_apple(logdate date);
1125 create table testtable_orange(logdate date);
1126 create index testtable_apple_index on testtable_apple(logdate);
1127 create index testtable_orange_index on testtable_orange(logdate);
1129 create table testpart_apple(logdate date) partition by range(logdate);
1130 create table testpart_orange(logdate date) partition by range(logdate);
1132 create index testpart_apple_index on testpart_apple(logdate);
1133 create index testpart_orange_index on testpart_orange(logdate);
1135 -- only partition related object should be displayed
1140 drop table testtable_apple;
1141 drop table testtable_orange;
1142 drop table testpart_apple;
1143 drop table testpart_orange;
1145 create table parent_tab (id int) partition by range (id);
1146 create index parent_index on parent_tab (id);
1147 create table child_0_10 partition of parent_tab
1148 for values from (0) to (10);
1149 create table child_10_20 partition of parent_tab
1150 for values from (10) to (20);
1151 create table child_20_30 partition of parent_tab
1152 for values from (20) to (30);
1153 insert into parent_tab values (generate_series(0,29));
1154 create table child_30_40 partition of parent_tab
1155 for values from (30) to (40)
1156 partition by range(id);
1157 create table child_30_35 partition of child_30_40
1158 for values from (30) to (35);
1159 create table child_35_40 partition of child_30_40
1160 for values from (35) to (40);
1161 insert into parent_tab values (generate_series(30,39));
1174 drop table parent_tab cascade;
1176 drop schema testpart;
1178 set search_path to default;
1180 set role to default;
1181 drop role regress_partitioning_role;
1183 -- \d on toast table (use pg_statistic's toast table, which has a known name)
1184 \d pg_toast.pg_toast_2619