1-- 2-- Tests for psql features that aren't closely connected to any 3-- specific server features 4-- 5 6-- \set 7 8-- fail: invalid name 9\set invalid/name foo 10-- fail: invalid value for special variable 11\set AUTOCOMMIT foo 12\set FETCH_COUNT foo 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 23 24-- \g and \gx 25 26SELECT 1 as one, 2 as two \g 27\gx 28SELECT 3 as three, 4 as four \gx 29\g 30 31-- \gx should work in FETCH_COUNT mode too 32\set FETCH_COUNT 1 33 34SELECT 1 as one, 2 as two \g 35\gx 36SELECT 3 as three, 4 as four \gx 37\g 38 39\unset FETCH_COUNT 40 41-- \g/\gx with pset options 42 43SELECT 1 as one, 2 as two \g (format=csv csv_fieldsep='\t') 44\g 45SELECT 1 as one, 2 as two \gx (title='foo bar') 46\g 47 48-- \gset 49 50select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_ 51 52\echo :pref01_test01 :pref01_test02 :pref01_test03 53 54-- should fail: bad variable name 55select 10 as "bad name" 56\gset 57 58select 97 as "EOF", 'ok' as _foo \gset IGNORE 59\echo :IGNORE_foo :IGNOREEOF 60 61-- multiple backslash commands in one line 62select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x 63select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y 64select 5 as x, 6 as y \gset pref01_ \\ \g \echo :pref01_x :pref01_y 65select 7 as x, 8 as y \g \gset pref01_ \echo :pref01_x :pref01_y 66 67-- NULL should unset the variable 68\set var2 xyz 69select 1 as var1, NULL as var2, 3 as var3 \gset 70\echo :var1 :var2 :var3 71 72-- \gset requires just one tuple 73select 10 as test01, 20 as test02 from generate_series(1,3) \gset 74select 10 as test01, 20 as test02 from generate_series(1,0) \gset 75 76-- \gset should work in FETCH_COUNT mode too 77\set FETCH_COUNT 1 78 79select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x 80select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y 81select 10 as test01, 20 as test02 from generate_series(1,3) \gset 82select 10 as test01, 20 as test02 from generate_series(1,0) \gset 83 84\unset FETCH_COUNT 85 86-- \gdesc 87 88SELECT 89 NULL AS zero, 90 1 AS one, 91 2.0 AS two, 92 'three' AS three, 93 $1 AS four, 94 sin($2) as five, 95 'foo'::varchar(4) as six, 96 CURRENT_DATE AS now 97\gdesc 98 99-- should work with tuple-returning utilities, such as EXECUTE 100PREPARE test AS SELECT 1 AS first, 2 AS second; 101EXECUTE test \gdesc 102EXPLAIN EXECUTE test \gdesc 103 104-- should fail cleanly - syntax error 105SELECT 1 + \gdesc 106 107-- check behavior with empty results 108SELECT \gdesc 109CREATE TABLE bububu(a int) \gdesc 110 111-- subject command should not have executed 112TABLE bububu; -- fail 113 114-- query buffer should remain unchanged 115SELECT 1 AS x, 'Hello', 2 AS y, true AS "dirty\name" 116\gdesc 117\g 118 119-- all on one line 120SELECT 3 AS x, 'Hello', 4 AS y, true AS "dirty\name" \gdesc \g 121 122-- \gexec 123 124create temporary table gexec_test(a int, b text, c date, d float); 125select format('create index on gexec_test(%I)', attname) 126from pg_attribute 127where attrelid = 'gexec_test'::regclass and attnum > 0 128order by attnum 129\gexec 130 131-- \gexec should work in FETCH_COUNT mode too 132-- (though the fetch limit applies to the executed queries not the meta query) 133\set FETCH_COUNT 1 134 135select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' 136union all 137select 'drop table gexec_test', NULL 138union all 139select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over' 140\gexec 141 142\unset FETCH_COUNT 143 144-- show all pset options 145\pset 146 147-- test multi-line headers, wrapping, and newline indicators 148-- in aligned, unaligned, and wrapped formats 149prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab 150 151c", array_to_string(array_agg(repeat('y',20-2*n)),E'\n') as "a 152bc" from generate_series(1,10) as n(n) group by n>1 order by n>1; 153 154\pset linestyle ascii 155 156\pset expanded off 157\pset columns 40 158 159\pset border 0 160\pset format unaligned 161execute q; 162\pset format aligned 163execute q; 164\pset format wrapped 165execute q; 166 167\pset border 1 168\pset format unaligned 169execute q; 170\pset format aligned 171execute q; 172\pset format wrapped 173execute q; 174 175\pset border 2 176\pset format unaligned 177execute q; 178\pset format aligned 179execute q; 180\pset format wrapped 181execute q; 182 183\pset expanded on 184\pset columns 20 185 186\pset border 0 187\pset format unaligned 188execute q; 189\pset format aligned 190execute q; 191\pset format wrapped 192execute q; 193 194\pset border 1 195\pset format unaligned 196execute q; 197\pset format aligned 198execute q; 199\pset format wrapped 200execute q; 201 202\pset border 2 203\pset format unaligned 204execute q; 205\pset format aligned 206execute q; 207\pset format wrapped 208execute q; 209 210\pset linestyle old-ascii 211 212\pset expanded off 213\pset columns 40 214 215\pset border 0 216\pset format unaligned 217execute q; 218\pset format aligned 219execute q; 220\pset format wrapped 221execute q; 222 223\pset border 1 224\pset format unaligned 225execute q; 226\pset format aligned 227execute q; 228\pset format wrapped 229execute q; 230 231\pset border 2 232\pset format unaligned 233execute q; 234\pset format aligned 235execute q; 236\pset format wrapped 237execute q; 238 239\pset expanded on 240\pset columns 20 241 242\pset border 0 243\pset format unaligned 244execute q; 245\pset format aligned 246execute q; 247\pset format wrapped 248execute q; 249 250\pset border 1 251\pset format unaligned 252execute q; 253\pset format aligned 254execute q; 255\pset format wrapped 256execute q; 257 258\pset border 2 259\pset format unaligned 260execute q; 261\pset format aligned 262execute q; 263\pset format wrapped 264execute q; 265 266deallocate q; 267 268-- test single-line header and data 269prepare q as select repeat('x',2*n) as "0123456789abcdef", repeat('y',20-2*n) as "0123456789" from generate_series(1,10) as n; 270 271\pset linestyle ascii 272 273\pset expanded off 274\pset columns 40 275 276\pset border 0 277\pset format unaligned 278execute q; 279\pset format aligned 280execute q; 281\pset format wrapped 282execute q; 283 284\pset border 1 285\pset format unaligned 286execute q; 287\pset format aligned 288execute q; 289\pset format wrapped 290execute q; 291 292\pset border 2 293\pset format unaligned 294execute q; 295\pset format aligned 296execute q; 297\pset format wrapped 298execute q; 299 300\pset expanded on 301\pset columns 30 302 303\pset border 0 304\pset format unaligned 305execute q; 306\pset format aligned 307execute q; 308\pset format wrapped 309execute q; 310 311\pset border 1 312\pset format unaligned 313execute q; 314\pset format aligned 315execute q; 316\pset format wrapped 317execute q; 318 319\pset border 2 320\pset format unaligned 321execute q; 322\pset format aligned 323execute q; 324\pset format wrapped 325execute q; 326 327\pset expanded on 328\pset columns 20 329 330\pset border 0 331\pset format unaligned 332execute q; 333\pset format aligned 334execute q; 335\pset format wrapped 336execute q; 337 338\pset border 1 339\pset format unaligned 340execute q; 341\pset format aligned 342execute q; 343\pset format wrapped 344execute q; 345 346\pset border 2 347\pset format unaligned 348execute q; 349\pset format aligned 350execute q; 351\pset format wrapped 352execute q; 353 354\pset linestyle old-ascii 355 356\pset expanded off 357\pset columns 40 358 359\pset border 0 360\pset format unaligned 361execute q; 362\pset format aligned 363execute q; 364\pset format wrapped 365execute q; 366 367\pset border 1 368\pset format unaligned 369execute q; 370\pset format aligned 371execute q; 372\pset format wrapped 373execute q; 374 375\pset border 2 376\pset format unaligned 377execute q; 378\pset format aligned 379execute q; 380\pset format wrapped 381execute q; 382 383\pset expanded on 384 385\pset border 0 386\pset format unaligned 387execute q; 388\pset format aligned 389execute q; 390\pset format wrapped 391execute q; 392 393\pset border 1 394\pset format unaligned 395execute q; 396\pset format aligned 397execute q; 398\pset format wrapped 399execute q; 400 401\pset border 2 402\pset format unaligned 403execute q; 404\pset format aligned 405execute q; 406\pset format wrapped 407execute q; 408 409deallocate q; 410 411\pset linestyle ascii 412\pset border 1 413 414-- support table for output-format tests (useful to create a footer) 415 416create table psql_serial_tab (id serial); 417 418-- test header/footer/tuples_only behavior in aligned/unaligned/wrapped cases 419 420\pset format aligned 421 422\pset expanded off 423\d psql_serial_tab_id_seq 424\pset tuples_only true 425\df exp 426\pset tuples_only false 427\pset expanded on 428\d psql_serial_tab_id_seq 429\pset tuples_only true 430\df exp 431\pset tuples_only false 432-- empty table is a special case for this format 433select 1 where false; 434 435\pset format unaligned 436 437\pset expanded off 438\d psql_serial_tab_id_seq 439\pset tuples_only true 440\df exp 441\pset tuples_only false 442\pset expanded on 443\d psql_serial_tab_id_seq 444\pset tuples_only true 445\df exp 446\pset tuples_only false 447 448\pset format wrapped 449 450\pset expanded off 451\d psql_serial_tab_id_seq 452\pset tuples_only true 453\df exp 454\pset tuples_only false 455\pset expanded on 456\d psql_serial_tab_id_seq 457\pset tuples_only true 458\df exp 459\pset tuples_only false 460 461-- check conditional am display 462\pset expanded off 463 464CREATE SCHEMA tableam_display; 465CREATE ROLE regress_display_role; 466ALTER SCHEMA tableam_display OWNER TO regress_display_role; 467SET search_path TO tableam_display; 468CREATE ACCESS METHOD heap_psql TYPE TABLE HANDLER heap_tableam_handler; 469SET ROLE TO regress_display_role; 470-- Use only relations with a physical size of zero. 471CREATE TABLE tbl_heap_psql(f1 int, f2 char(100)) using heap_psql; 472CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap; 473CREATE VIEW view_heap_psql AS SELECT f1 from tbl_heap_psql; 474CREATE MATERIALIZED VIEW mat_view_heap_psql USING heap_psql AS SELECT f1 from tbl_heap_psql; 475\d+ tbl_heap_psql 476\d+ tbl_heap 477\set HIDE_TABLEAM off 478\d+ tbl_heap_psql 479\d+ tbl_heap 480-- AM is displayed for tables, indexes and materialized views. 481\d+ 482\dt+ 483\dm+ 484-- But not for views and sequences. 485\dv+ 486\set HIDE_TABLEAM on 487\d+ 488RESET ROLE; 489RESET search_path; 490DROP SCHEMA tableam_display CASCADE; 491DROP ACCESS METHOD heap_psql; 492DROP ROLE regress_display_role; 493 494-- test numericlocale (as best we can without control of psql's locale) 495 496\pset format aligned 497\pset expanded off 498\pset numericlocale true 499 500select n, -n as m, n * 111 as x, '1e90'::float8 as f 501from generate_series(0,3) n; 502 503\pset numericlocale false 504 505-- test asciidoc output format 506 507\pset format asciidoc 508 509\pset border 1 510\pset expanded off 511\d psql_serial_tab_id_seq 512\pset tuples_only true 513\df exp 514\pset tuples_only false 515\pset expanded on 516\d psql_serial_tab_id_seq 517\pset tuples_only true 518\df exp 519\pset tuples_only false 520 521prepare q as 522 select 'some|text' as "a|title", ' ' as "empty ", n as int 523 from generate_series(1,2) as n; 524 525\pset expanded off 526\pset border 0 527execute q; 528 529\pset border 1 530execute q; 531 532\pset border 2 533execute q; 534 535\pset expanded on 536\pset border 0 537execute q; 538 539\pset border 1 540execute q; 541 542\pset border 2 543execute q; 544 545deallocate q; 546 547-- test csv output format 548 549\pset format csv 550 551\pset border 1 552\pset expanded off 553\d psql_serial_tab_id_seq 554\pset tuples_only true 555\df exp 556\pset tuples_only false 557\pset expanded on 558\d psql_serial_tab_id_seq 559\pset tuples_only true 560\df exp 561\pset tuples_only false 562 563prepare q as 564 select 'some"text' as "a""title", E' <foo>\n<bar>' as "junk", 565 ' ' as "empty", n as int 566 from generate_series(1,2) as n; 567 568\pset expanded off 569execute q; 570 571\pset expanded on 572execute q; 573 574deallocate q; 575 576-- special cases 577\pset expanded off 578select 'comma,comma' as comma, 'semi;semi' as semi; 579\pset csv_fieldsep ';' 580select 'comma,comma' as comma, 'semi;semi' as semi; 581select '\.' as data; 582\pset csv_fieldsep '.' 583select '\' as d1, '' as d2; 584 585-- illegal csv separators 586\pset csv_fieldsep '' 587\pset csv_fieldsep '\0' 588\pset csv_fieldsep '\n' 589\pset csv_fieldsep '\r' 590\pset csv_fieldsep '"' 591\pset csv_fieldsep ',,' 592 593\pset csv_fieldsep ',' 594 595-- test html output format 596 597\pset format html 598 599\pset border 1 600\pset expanded off 601\d psql_serial_tab_id_seq 602\pset tuples_only true 603\df exp 604\pset tuples_only false 605\pset expanded on 606\d psql_serial_tab_id_seq 607\pset tuples_only true 608\df exp 609\pset tuples_only false 610 611prepare q as 612 select 'some"text' as "a&title", E' <foo>\n<bar>' as "junk", 613 ' ' as "empty", n as int 614 from generate_series(1,2) as n; 615 616\pset expanded off 617\pset border 0 618execute q; 619 620\pset border 1 621execute q; 622 623\pset tableattr foobar 624execute q; 625\pset tableattr 626 627\pset expanded on 628\pset border 0 629execute q; 630 631\pset border 1 632execute q; 633 634\pset tableattr foobar 635execute q; 636\pset tableattr 637 638deallocate q; 639 640-- test latex output format 641 642\pset format latex 643 644\pset border 1 645\pset expanded off 646\d psql_serial_tab_id_seq 647\pset tuples_only true 648\df exp 649\pset tuples_only false 650\pset expanded on 651\d psql_serial_tab_id_seq 652\pset tuples_only true 653\df exp 654\pset tuples_only false 655 656prepare q as 657 select 'some\more_text' as "a$title", E' #<foo>%&^~|\n{bar}' as "junk", 658 ' ' as "empty", n as int 659 from generate_series(1,2) as n; 660 661\pset expanded off 662\pset border 0 663execute q; 664 665\pset border 1 666execute q; 667 668\pset border 2 669execute q; 670 671\pset border 3 672execute q; 673 674\pset expanded on 675\pset border 0 676execute q; 677 678\pset border 1 679execute q; 680 681\pset border 2 682execute q; 683 684\pset border 3 685execute q; 686 687deallocate q; 688 689-- test latex-longtable output format 690 691\pset format latex-longtable 692 693\pset border 1 694\pset expanded off 695\d psql_serial_tab_id_seq 696\pset tuples_only true 697\df exp 698\pset tuples_only false 699\pset expanded on 700\d psql_serial_tab_id_seq 701\pset tuples_only true 702\df exp 703\pset tuples_only false 704 705prepare q as 706 select 'some\more_text' as "a$title", E' #<foo>%&^~|\n{bar}' as "junk", 707 ' ' as "empty", n as int 708 from generate_series(1,2) as n; 709 710\pset expanded off 711\pset border 0 712execute q; 713 714\pset border 1 715execute q; 716 717\pset border 2 718execute q; 719 720\pset border 3 721execute q; 722 723\pset tableattr lr 724execute q; 725\pset tableattr 726 727\pset expanded on 728\pset border 0 729execute q; 730 731\pset border 1 732execute q; 733 734\pset border 2 735execute q; 736 737\pset border 3 738execute q; 739 740\pset tableattr lr 741execute q; 742\pset tableattr 743 744deallocate q; 745 746-- test troff-ms output format 747 748\pset format troff-ms 749 750\pset border 1 751\pset expanded off 752\d psql_serial_tab_id_seq 753\pset tuples_only true 754\df exp 755\pset tuples_only false 756\pset expanded on 757\d psql_serial_tab_id_seq 758\pset tuples_only true 759\df exp 760\pset tuples_only false 761 762prepare q as 763 select 'some\text' as "a\title", E' <foo>\n<bar>' as "junk", 764 ' ' as "empty", n as int 765 from generate_series(1,2) as n; 766 767\pset expanded off 768\pset border 0 769execute q; 770 771\pset border 1 772execute q; 773 774\pset border 2 775execute q; 776 777\pset expanded on 778\pset border 0 779execute q; 780 781\pset border 1 782execute q; 783 784\pset border 2 785execute q; 786 787deallocate q; 788 789-- check ambiguous format requests 790 791\pset format a 792\pset format l 793 794-- clean up after output format tests 795 796drop table psql_serial_tab; 797 798\pset format aligned 799\pset expanded off 800\pset border 1 801 802-- \echo and allied features 803 804\echo this is a test 805\echo -n without newline 806\echo with -n newline 807\echo '-n' with newline 808 809\set foo bar 810\echo foo = :foo 811 812\qecho this is a test 813\qecho foo = :foo 814 815\warn this is a test 816\warn foo = :foo 817 818-- tests for \if ... \endif 819 820\if true 821 select 'okay'; 822 select 'still okay'; 823\else 824 not okay; 825 still not okay 826\endif 827 828-- at this point query buffer should still have last valid line 829\g 830 831-- \if should work okay on part of a query 832select 833 \if true 834 42 835 \else 836 (bogus 837 \endif 838 forty_two; 839 840select \if false \\ (bogus \else \\ 42 \endif \\ forty_two; 841 842-- test a large nested if using a variety of true-equivalents 843\if true 844 \if 1 845 \if yes 846 \if on 847 \echo 'all true' 848 \else 849 \echo 'should not print #1-1' 850 \endif 851 \else 852 \echo 'should not print #1-2' 853 \endif 854 \else 855 \echo 'should not print #1-3' 856 \endif 857\else 858 \echo 'should not print #1-4' 859\endif 860 861-- test a variety of false-equivalents in an if/elif/else structure 862\if false 863 \echo 'should not print #2-1' 864\elif 0 865 \echo 'should not print #2-2' 866\elif no 867 \echo 'should not print #2-3' 868\elif off 869 \echo 'should not print #2-4' 870\else 871 \echo 'all false' 872\endif 873 874-- test true-false elif after initial true branch 875\if true 876 \echo 'should print #2-5' 877\elif true 878 \echo 'should not print #2-6' 879\elif false 880 \echo 'should not print #2-7' 881\else 882 \echo 'should not print #2-8' 883\endif 884 885-- test simple true-then-else 886\if true 887 \echo 'first thing true' 888\else 889 \echo 'should not print #3-1' 890\endif 891 892-- test simple false-true-else 893\if false 894 \echo 'should not print #4-1' 895\elif true 896 \echo 'second thing true' 897\else 898 \echo 'should not print #5-1' 899\endif 900 901-- invalid boolean expressions are false 902\if invalid boolean expression 903 \echo 'will not print #6-1' 904\else 905 \echo 'will print anyway #6-2' 906\endif 907 908-- test un-matched endif 909\endif 910 911-- test un-matched else 912\else 913 914-- test un-matched elif 915\elif 916 917-- test double-else error 918\if true 919\else 920\else 921\endif 922 923-- test elif out-of-order 924\if false 925\else 926\elif 927\endif 928 929-- test if-endif matching in a false branch 930\if false 931 \if false 932 \echo 'should not print #7-1' 933 \else 934 \echo 'should not print #7-2' 935 \endif 936 \echo 'should not print #7-3' 937\else 938 \echo 'should print #7-4' 939\endif 940 941-- show that vars and backticks are not expanded when ignoring extra args 942\set foo bar 943\echo :foo :'foo' :"foo" 944\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo" 945 946-- show that vars and backticks are not expanded and commands are ignored 947-- when in a false if-branch 948\set try_to_quit '\\q' 949\if false 950 :try_to_quit 951 \echo `nosuchcommand` :foo :'foo' :"foo" 952 \pset fieldsep | `nosuchcommand` :foo :'foo' :"foo" 953 \a 954 \C arg1 955 \c arg1 arg2 arg3 arg4 956 \cd arg1 957 \conninfo 958 \copy arg1 arg2 arg3 arg4 arg5 arg6 959 \copyright 960 SELECT 1 as one, 2, 3 \crosstabview 961 \dt arg1 962 \e arg1 arg2 963 \ef whole_line 964 \ev whole_line 965 \echo arg1 arg2 arg3 arg4 arg5 966 \echo arg1 967 \encoding arg1 968 \errverbose 969 \f arg1 970 \g arg1 971 \gx arg1 972 \gexec 973 SELECT 1 AS one \gset 974 \h 975 \? 976 \html 977 \i arg1 978 \ir arg1 979 \l arg1 980 \lo arg1 arg2 981 \lo_list 982 \o arg1 983 \p 984 \password arg1 985 \prompt arg1 arg2 986 \pset arg1 arg2 987 \q 988 \reset 989 \s arg1 990 \set arg1 arg2 arg3 arg4 arg5 arg6 arg7 991 \setenv arg1 arg2 992 \sf whole_line 993 \sv whole_line 994 \t arg1 995 \T arg1 996 \timing arg1 997 \unset arg1 998 \w arg1 999 \watch arg1 1000 \x arg1 1001 -- \else here is eaten as part of OT_FILEPIPE argument 1002 \w |/no/such/file \else 1003 -- \endif here is eaten as part of whole-line argument 1004 \! whole_line \endif 1005 \z 1006\else 1007 \echo 'should print #8-1' 1008\endif 1009 1010-- :{?...} defined variable test 1011\set i 1 1012\if :{?i} 1013 \echo '#9-1 ok, variable i is defined' 1014\else 1015 \echo 'should not print #9-2' 1016\endif 1017 1018\if :{?no_such_variable} 1019 \echo 'should not print #10-1' 1020\else 1021 \echo '#10-2 ok, variable no_such_variable is not defined' 1022\endif 1023 1024SELECT :{?i} AS i_is_defined; 1025 1026SELECT NOT :{?no_such_var} AS no_such_var_is_not_defined; 1027 1028-- SHOW_CONTEXT 1029 1030\set SHOW_CONTEXT never 1031do $$ 1032begin 1033 raise notice 'foo'; 1034 raise exception 'bar'; 1035end $$; 1036 1037\set SHOW_CONTEXT errors 1038do $$ 1039begin 1040 raise notice 'foo'; 1041 raise exception 'bar'; 1042end $$; 1043 1044\set SHOW_CONTEXT always 1045do $$ 1046begin 1047 raise notice 'foo'; 1048 raise exception 'bar'; 1049end $$; 1050 1051-- test printing and clearing the query buffer 1052SELECT 1; 1053\p 1054SELECT 2 \r 1055\p 1056SELECT 3 \p 1057UNION SELECT 4 \p 1058UNION SELECT 5 1059ORDER BY 1; 1060\r 1061\p 1062 1063-- tests for special result variables 1064 1065-- working query, 2 rows selected 1066SELECT 1 AS stuff UNION SELECT 2; 1067\echo 'error:' :ERROR 1068\echo 'error code:' :SQLSTATE 1069\echo 'number of rows:' :ROW_COUNT 1070 1071-- syntax error 1072SELECT 1 UNION; 1073\echo 'error:' :ERROR 1074\echo 'error code:' :SQLSTATE 1075\echo 'number of rows:' :ROW_COUNT 1076\echo 'last error message:' :LAST_ERROR_MESSAGE 1077\echo 'last error code:' :LAST_ERROR_SQLSTATE 1078 1079-- empty query 1080; 1081\echo 'error:' :ERROR 1082\echo 'error code:' :SQLSTATE 1083\echo 'number of rows:' :ROW_COUNT 1084-- must have kept previous values 1085\echo 'last error message:' :LAST_ERROR_MESSAGE 1086\echo 'last error code:' :LAST_ERROR_SQLSTATE 1087 1088-- other query error 1089DROP TABLE this_table_does_not_exist; 1090\echo 'error:' :ERROR 1091\echo 'error code:' :SQLSTATE 1092\echo 'number of rows:' :ROW_COUNT 1093\echo 'last error message:' :LAST_ERROR_MESSAGE 1094\echo 'last error code:' :LAST_ERROR_SQLSTATE 1095 1096-- nondefault verbosity error settings (except verbose, which is too unstable) 1097\set VERBOSITY terse 1098SELECT 1 UNION; 1099\echo 'error:' :ERROR 1100\echo 'error code:' :SQLSTATE 1101\echo 'last error message:' :LAST_ERROR_MESSAGE 1102 1103\set VERBOSITY sqlstate 1104SELECT 1/0; 1105\echo 'error:' :ERROR 1106\echo 'error code:' :SQLSTATE 1107\echo 'last error message:' :LAST_ERROR_MESSAGE 1108 1109\set VERBOSITY default 1110 1111-- working \gdesc 1112SELECT 3 AS three, 4 AS four \gdesc 1113\echo 'error:' :ERROR 1114\echo 'error code:' :SQLSTATE 1115\echo 'number of rows:' :ROW_COUNT 1116 1117-- \gdesc with an error 1118SELECT 4 AS \gdesc 1119\echo 'error:' :ERROR 1120\echo 'error code:' :SQLSTATE 1121\echo 'number of rows:' :ROW_COUNT 1122\echo 'last error message:' :LAST_ERROR_MESSAGE 1123\echo 'last error code:' :LAST_ERROR_SQLSTATE 1124 1125-- check row count for a cursor-fetched query 1126\set FETCH_COUNT 10 1127select unique2 from tenk1 order by unique2 limit 19; 1128\echo 'error:' :ERROR 1129\echo 'error code:' :SQLSTATE 1130\echo 'number of rows:' :ROW_COUNT 1131 1132-- cursor-fetched query with an error after the first group 1133select 1/(15-unique2) from tenk1 order by unique2 limit 19; 1134\echo 'error:' :ERROR 1135\echo 'error code:' :SQLSTATE 1136\echo 'number of rows:' :ROW_COUNT 1137\echo 'last error message:' :LAST_ERROR_MESSAGE 1138\echo 'last error code:' :LAST_ERROR_SQLSTATE 1139 1140\unset FETCH_COUNT 1141 1142create schema testpart; 1143create role regress_partitioning_role; 1144 1145alter schema testpart owner to regress_partitioning_role; 1146 1147set role to regress_partitioning_role; 1148 1149-- run test inside own schema and hide other partitions 1150set search_path to testpart; 1151 1152create table testtable_apple(logdate date); 1153create table testtable_orange(logdate date); 1154create index testtable_apple_index on testtable_apple(logdate); 1155create index testtable_orange_index on testtable_orange(logdate); 1156 1157create table testpart_apple(logdate date) partition by range(logdate); 1158create table testpart_orange(logdate date) partition by range(logdate); 1159 1160create index testpart_apple_index on testpart_apple(logdate); 1161create index testpart_orange_index on testpart_orange(logdate); 1162 1163-- only partition related object should be displayed 1164\dP test*apple* 1165\dPt test*apple* 1166\dPi test*apple* 1167 1168drop table testtable_apple; 1169drop table testtable_orange; 1170drop table testpart_apple; 1171drop table testpart_orange; 1172 1173create table parent_tab (id int) partition by range (id); 1174create index parent_index on parent_tab (id); 1175create table child_0_10 partition of parent_tab 1176 for values from (0) to (10); 1177create table child_10_20 partition of parent_tab 1178 for values from (10) to (20); 1179create table child_20_30 partition of parent_tab 1180 for values from (20) to (30); 1181insert into parent_tab values (generate_series(0,29)); 1182create table child_30_40 partition of parent_tab 1183for values from (30) to (40) 1184 partition by range(id); 1185create table child_30_35 partition of child_30_40 1186 for values from (30) to (35); 1187create table child_35_40 partition of child_30_40 1188 for values from (35) to (40); 1189insert into parent_tab values (generate_series(30,39)); 1190 1191\dPt 1192\dPi 1193 1194\dP testpart.* 1195\dP 1196 1197\dPtn 1198\dPin 1199\dPn 1200\dPn testpart.* 1201 1202drop table parent_tab cascade; 1203 1204drop schema testpart; 1205 1206set search_path to default; 1207 1208set role to default; 1209drop role regress_partitioning_role; 1210 1211-- \d on toast table (use pg_statistic's toast table, which has a known name) 1212\d pg_toast.pg_toast_2619 1213 1214-- check printing info about access methods 1215\dA 1216\dA * 1217\dA h* 1218\dA foo 1219\dA foo bar 1220\dA+ 1221\dA+ * 1222\dA+ h* 1223\dA+ foo 1224\dAc brin pg*.oid* 1225\dAf spgist 1226\dAf btree int4 1227\dAo+ btree float_ops 1228\dAo * pg_catalog.jsonb_path_ops 1229\dAp+ btree float_ops 1230\dAp * pg_catalog.uuid_ops 1231 1232-- check \df, \do with argument specifications 1233\df *sqrt 1234\df *sqrt num* 1235\df int*pl 1236\df int*pl int4 1237\df int*pl * pg_catalog.int8 1238\df acl* aclitem[] 1239\df has_database_privilege oid text 1240\df has_database_privilege oid text - 1241\dfa bit* small* 1242\do - pg_catalog.int4 1243\do && anyarray * 1244