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 tableam display 462 463-- Create a heap2 table am handler with heapam handler 464CREATE ACCESS METHOD heap_psql TYPE TABLE HANDLER heap_tableam_handler; 465CREATE TABLE tbl_heap_psql(f1 int, f2 char(100)) using heap_psql; 466CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap; 467\d+ tbl_heap_psql 468\d+ tbl_heap 469\set HIDE_TABLEAM off 470\d+ tbl_heap_psql 471\d+ tbl_heap 472\set HIDE_TABLEAM on 473DROP TABLE tbl_heap, tbl_heap_psql; 474DROP ACCESS METHOD heap_psql; 475 476-- test numericlocale (as best we can without control of psql's locale) 477 478\pset format aligned 479\pset expanded off 480\pset numericlocale true 481 482select n, -n as m, n * 111 as x, '1e90'::float8 as f 483from generate_series(0,3) n; 484 485\pset numericlocale false 486 487-- test asciidoc output format 488 489\pset format asciidoc 490 491\pset border 1 492\pset expanded off 493\d psql_serial_tab_id_seq 494\pset tuples_only true 495\df exp 496\pset tuples_only false 497\pset expanded on 498\d psql_serial_tab_id_seq 499\pset tuples_only true 500\df exp 501\pset tuples_only false 502 503prepare q as 504 select 'some|text' as "a|title", ' ' as "empty ", n as int 505 from generate_series(1,2) as n; 506 507\pset expanded off 508\pset border 0 509execute q; 510 511\pset border 1 512execute q; 513 514\pset border 2 515execute q; 516 517\pset expanded on 518\pset border 0 519execute q; 520 521\pset border 1 522execute q; 523 524\pset border 2 525execute q; 526 527deallocate q; 528 529-- test csv output format 530 531\pset format csv 532 533\pset border 1 534\pset expanded off 535\d psql_serial_tab_id_seq 536\pset tuples_only true 537\df exp 538\pset tuples_only false 539\pset expanded on 540\d psql_serial_tab_id_seq 541\pset tuples_only true 542\df exp 543\pset tuples_only false 544 545prepare q as 546 select 'some"text' as "a""title", E' <foo>\n<bar>' as "junk", 547 ' ' as "empty", n as int 548 from generate_series(1,2) as n; 549 550\pset expanded off 551execute q; 552 553\pset expanded on 554execute q; 555 556deallocate q; 557 558-- special cases 559\pset expanded off 560select 'comma,comma' as comma, 'semi;semi' as semi; 561\pset csv_fieldsep ';' 562select 'comma,comma' as comma, 'semi;semi' as semi; 563select '\.' as data; 564\pset csv_fieldsep '.' 565select '\' as d1, '' as d2; 566 567-- illegal csv separators 568\pset csv_fieldsep '' 569\pset csv_fieldsep '\0' 570\pset csv_fieldsep '\n' 571\pset csv_fieldsep '\r' 572\pset csv_fieldsep '"' 573\pset csv_fieldsep ',,' 574 575\pset csv_fieldsep ',' 576 577-- test html output format 578 579\pset format html 580 581\pset border 1 582\pset expanded off 583\d psql_serial_tab_id_seq 584\pset tuples_only true 585\df exp 586\pset tuples_only false 587\pset expanded on 588\d psql_serial_tab_id_seq 589\pset tuples_only true 590\df exp 591\pset tuples_only false 592 593prepare q as 594 select 'some"text' as "a&title", E' <foo>\n<bar>' as "junk", 595 ' ' as "empty", n as int 596 from generate_series(1,2) as n; 597 598\pset expanded off 599\pset border 0 600execute q; 601 602\pset border 1 603execute q; 604 605\pset tableattr foobar 606execute q; 607\pset tableattr 608 609\pset expanded on 610\pset border 0 611execute q; 612 613\pset border 1 614execute q; 615 616\pset tableattr foobar 617execute q; 618\pset tableattr 619 620deallocate q; 621 622-- test latex output format 623 624\pset format latex 625 626\pset border 1 627\pset expanded off 628\d psql_serial_tab_id_seq 629\pset tuples_only true 630\df exp 631\pset tuples_only false 632\pset expanded on 633\d psql_serial_tab_id_seq 634\pset tuples_only true 635\df exp 636\pset tuples_only false 637 638prepare q as 639 select 'some\more_text' as "a$title", E' #<foo>%&^~|\n{bar}' as "junk", 640 ' ' as "empty", n as int 641 from generate_series(1,2) as n; 642 643\pset expanded off 644\pset border 0 645execute q; 646 647\pset border 1 648execute q; 649 650\pset border 2 651execute q; 652 653\pset border 3 654execute q; 655 656\pset expanded on 657\pset border 0 658execute q; 659 660\pset border 1 661execute q; 662 663\pset border 2 664execute q; 665 666\pset border 3 667execute q; 668 669deallocate q; 670 671-- test latex-longtable output format 672 673\pset format latex-longtable 674 675\pset border 1 676\pset expanded off 677\d psql_serial_tab_id_seq 678\pset tuples_only true 679\df exp 680\pset tuples_only false 681\pset expanded on 682\d psql_serial_tab_id_seq 683\pset tuples_only true 684\df exp 685\pset tuples_only false 686 687prepare q as 688 select 'some\more_text' as "a$title", E' #<foo>%&^~|\n{bar}' as "junk", 689 ' ' as "empty", n as int 690 from generate_series(1,2) as n; 691 692\pset expanded off 693\pset border 0 694execute q; 695 696\pset border 1 697execute q; 698 699\pset border 2 700execute q; 701 702\pset border 3 703execute q; 704 705\pset tableattr lr 706execute q; 707\pset tableattr 708 709\pset expanded on 710\pset border 0 711execute q; 712 713\pset border 1 714execute q; 715 716\pset border 2 717execute q; 718 719\pset border 3 720execute q; 721 722\pset tableattr lr 723execute q; 724\pset tableattr 725 726deallocate q; 727 728-- test troff-ms output format 729 730\pset format troff-ms 731 732\pset border 1 733\pset expanded off 734\d psql_serial_tab_id_seq 735\pset tuples_only true 736\df exp 737\pset tuples_only false 738\pset expanded on 739\d psql_serial_tab_id_seq 740\pset tuples_only true 741\df exp 742\pset tuples_only false 743 744prepare q as 745 select 'some\text' as "a\title", E' <foo>\n<bar>' as "junk", 746 ' ' as "empty", n as int 747 from generate_series(1,2) as n; 748 749\pset expanded off 750\pset border 0 751execute q; 752 753\pset border 1 754execute q; 755 756\pset border 2 757execute q; 758 759\pset expanded on 760\pset border 0 761execute q; 762 763\pset border 1 764execute q; 765 766\pset border 2 767execute q; 768 769deallocate q; 770 771-- check ambiguous format requests 772 773\pset format a 774\pset format l 775 776-- clean up after output format tests 777 778drop table psql_serial_tab; 779 780\pset format aligned 781\pset expanded off 782\pset border 1 783 784-- \echo and allied features 785 786\echo this is a test 787\echo -n without newline 788\echo with -n newline 789\echo '-n' with newline 790 791\set foo bar 792\echo foo = :foo 793 794\qecho this is a test 795\qecho foo = :foo 796 797\warn this is a test 798\warn foo = :foo 799 800-- tests for \if ... \endif 801 802\if true 803 select 'okay'; 804 select 'still okay'; 805\else 806 not okay; 807 still not okay 808\endif 809 810-- at this point query buffer should still have last valid line 811\g 812 813-- \if should work okay on part of a query 814select 815 \if true 816 42 817 \else 818 (bogus 819 \endif 820 forty_two; 821 822select \if false \\ (bogus \else \\ 42 \endif \\ forty_two; 823 824-- test a large nested if using a variety of true-equivalents 825\if true 826 \if 1 827 \if yes 828 \if on 829 \echo 'all true' 830 \else 831 \echo 'should not print #1-1' 832 \endif 833 \else 834 \echo 'should not print #1-2' 835 \endif 836 \else 837 \echo 'should not print #1-3' 838 \endif 839\else 840 \echo 'should not print #1-4' 841\endif 842 843-- test a variety of false-equivalents in an if/elif/else structure 844\if false 845 \echo 'should not print #2-1' 846\elif 0 847 \echo 'should not print #2-2' 848\elif no 849 \echo 'should not print #2-3' 850\elif off 851 \echo 'should not print #2-4' 852\else 853 \echo 'all false' 854\endif 855 856-- test true-false elif after initial true branch 857\if true 858 \echo 'should print #2-5' 859\elif true 860 \echo 'should not print #2-6' 861\elif false 862 \echo 'should not print #2-7' 863\else 864 \echo 'should not print #2-8' 865\endif 866 867-- test simple true-then-else 868\if true 869 \echo 'first thing true' 870\else 871 \echo 'should not print #3-1' 872\endif 873 874-- test simple false-true-else 875\if false 876 \echo 'should not print #4-1' 877\elif true 878 \echo 'second thing true' 879\else 880 \echo 'should not print #5-1' 881\endif 882 883-- invalid boolean expressions are false 884\if invalid boolean expression 885 \echo 'will not print #6-1' 886\else 887 \echo 'will print anyway #6-2' 888\endif 889 890-- test un-matched endif 891\endif 892 893-- test un-matched else 894\else 895 896-- test un-matched elif 897\elif 898 899-- test double-else error 900\if true 901\else 902\else 903\endif 904 905-- test elif out-of-order 906\if false 907\else 908\elif 909\endif 910 911-- test if-endif matching in a false branch 912\if false 913 \if false 914 \echo 'should not print #7-1' 915 \else 916 \echo 'should not print #7-2' 917 \endif 918 \echo 'should not print #7-3' 919\else 920 \echo 'should print #7-4' 921\endif 922 923-- show that vars and backticks are not expanded when ignoring extra args 924\set foo bar 925\echo :foo :'foo' :"foo" 926\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo" 927 928-- show that vars and backticks are not expanded and commands are ignored 929-- when in a false if-branch 930\set try_to_quit '\\q' 931\if false 932 :try_to_quit 933 \echo `nosuchcommand` :foo :'foo' :"foo" 934 \pset fieldsep | `nosuchcommand` :foo :'foo' :"foo" 935 \a 936 \C arg1 937 \c arg1 arg2 arg3 arg4 938 \cd arg1 939 \conninfo 940 \copy arg1 arg2 arg3 arg4 arg5 arg6 941 \copyright 942 SELECT 1 as one, 2, 3 \crosstabview 943 \dt arg1 944 \e arg1 arg2 945 \ef whole_line 946 \ev whole_line 947 \echo arg1 arg2 arg3 arg4 arg5 948 \echo arg1 949 \encoding arg1 950 \errverbose 951 \f arg1 952 \g arg1 953 \gx arg1 954 \gexec 955 SELECT 1 AS one \gset 956 \h 957 \? 958 \html 959 \i arg1 960 \ir arg1 961 \l arg1 962 \lo arg1 arg2 963 \lo_list 964 \o arg1 965 \p 966 \password arg1 967 \prompt arg1 arg2 968 \pset arg1 arg2 969 \q 970 \reset 971 \s arg1 972 \set arg1 arg2 arg3 arg4 arg5 arg6 arg7 973 \setenv arg1 arg2 974 \sf whole_line 975 \sv whole_line 976 \t arg1 977 \T arg1 978 \timing arg1 979 \unset arg1 980 \w arg1 981 \watch arg1 982 \x arg1 983 -- \else here is eaten as part of OT_FILEPIPE argument 984 \w |/no/such/file \else 985 -- \endif here is eaten as part of whole-line argument 986 \! whole_line \endif 987 \z 988\else 989 \echo 'should print #8-1' 990\endif 991 992-- :{?...} defined variable test 993\set i 1 994\if :{?i} 995 \echo '#9-1 ok, variable i is defined' 996\else 997 \echo 'should not print #9-2' 998\endif 999 1000\if :{?no_such_variable} 1001 \echo 'should not print #10-1' 1002\else 1003 \echo '#10-2 ok, variable no_such_variable is not defined' 1004\endif 1005 1006SELECT :{?i} AS i_is_defined; 1007 1008SELECT NOT :{?no_such_var} AS no_such_var_is_not_defined; 1009 1010-- SHOW_CONTEXT 1011 1012\set SHOW_CONTEXT never 1013do $$ 1014begin 1015 raise notice 'foo'; 1016 raise exception 'bar'; 1017end $$; 1018 1019\set SHOW_CONTEXT errors 1020do $$ 1021begin 1022 raise notice 'foo'; 1023 raise exception 'bar'; 1024end $$; 1025 1026\set SHOW_CONTEXT always 1027do $$ 1028begin 1029 raise notice 'foo'; 1030 raise exception 'bar'; 1031end $$; 1032 1033-- test printing and clearing the query buffer 1034SELECT 1; 1035\p 1036SELECT 2 \r 1037\p 1038SELECT 3 \p 1039UNION SELECT 4 \p 1040UNION SELECT 5 1041ORDER BY 1; 1042\r 1043\p 1044 1045-- tests for special result variables 1046 1047-- working query, 2 rows selected 1048SELECT 1 AS stuff UNION SELECT 2; 1049\echo 'error:' :ERROR 1050\echo 'error code:' :SQLSTATE 1051\echo 'number of rows:' :ROW_COUNT 1052 1053-- syntax error 1054SELECT 1 UNION; 1055\echo 'error:' :ERROR 1056\echo 'error code:' :SQLSTATE 1057\echo 'number of rows:' :ROW_COUNT 1058\echo 'last error message:' :LAST_ERROR_MESSAGE 1059\echo 'last error code:' :LAST_ERROR_SQLSTATE 1060 1061-- empty query 1062; 1063\echo 'error:' :ERROR 1064\echo 'error code:' :SQLSTATE 1065\echo 'number of rows:' :ROW_COUNT 1066-- must have kept previous values 1067\echo 'last error message:' :LAST_ERROR_MESSAGE 1068\echo 'last error code:' :LAST_ERROR_SQLSTATE 1069 1070-- other query error 1071DROP TABLE this_table_does_not_exist; 1072\echo 'error:' :ERROR 1073\echo 'error code:' :SQLSTATE 1074\echo 'number of rows:' :ROW_COUNT 1075\echo 'last error message:' :LAST_ERROR_MESSAGE 1076\echo 'last error code:' :LAST_ERROR_SQLSTATE 1077 1078-- nondefault verbosity error settings (except verbose, which is too unstable) 1079\set VERBOSITY terse 1080SELECT 1 UNION; 1081\echo 'error:' :ERROR 1082\echo 'error code:' :SQLSTATE 1083\echo 'last error message:' :LAST_ERROR_MESSAGE 1084 1085\set VERBOSITY sqlstate 1086SELECT 1/0; 1087\echo 'error:' :ERROR 1088\echo 'error code:' :SQLSTATE 1089\echo 'last error message:' :LAST_ERROR_MESSAGE 1090 1091\set VERBOSITY default 1092 1093-- working \gdesc 1094SELECT 3 AS three, 4 AS four \gdesc 1095\echo 'error:' :ERROR 1096\echo 'error code:' :SQLSTATE 1097\echo 'number of rows:' :ROW_COUNT 1098 1099-- \gdesc with an error 1100SELECT 4 AS \gdesc 1101\echo 'error:' :ERROR 1102\echo 'error code:' :SQLSTATE 1103\echo 'number of rows:' :ROW_COUNT 1104\echo 'last error message:' :LAST_ERROR_MESSAGE 1105\echo 'last error code:' :LAST_ERROR_SQLSTATE 1106 1107-- check row count for a cursor-fetched query 1108\set FETCH_COUNT 10 1109select unique2 from tenk1 order by unique2 limit 19; 1110\echo 'error:' :ERROR 1111\echo 'error code:' :SQLSTATE 1112\echo 'number of rows:' :ROW_COUNT 1113 1114-- cursor-fetched query with an error after the first group 1115select 1/(15-unique2) from tenk1 order by unique2 limit 19; 1116\echo 'error:' :ERROR 1117\echo 'error code:' :SQLSTATE 1118\echo 'number of rows:' :ROW_COUNT 1119\echo 'last error message:' :LAST_ERROR_MESSAGE 1120\echo 'last error code:' :LAST_ERROR_SQLSTATE 1121 1122\unset FETCH_COUNT 1123 1124create schema testpart; 1125create role regress_partitioning_role; 1126 1127alter schema testpart owner to regress_partitioning_role; 1128 1129set role to regress_partitioning_role; 1130 1131-- run test inside own schema and hide other partitions 1132set search_path to testpart; 1133 1134create table testtable_apple(logdate date); 1135create table testtable_orange(logdate date); 1136create index testtable_apple_index on testtable_apple(logdate); 1137create index testtable_orange_index on testtable_orange(logdate); 1138 1139create table testpart_apple(logdate date) partition by range(logdate); 1140create table testpart_orange(logdate date) partition by range(logdate); 1141 1142create index testpart_apple_index on testpart_apple(logdate); 1143create index testpart_orange_index on testpart_orange(logdate); 1144 1145-- only partition related object should be displayed 1146\dP test*apple* 1147\dPt test*apple* 1148\dPi test*apple* 1149 1150drop table testtable_apple; 1151drop table testtable_orange; 1152drop table testpart_apple; 1153drop table testpart_orange; 1154 1155create table parent_tab (id int) partition by range (id); 1156create index parent_index on parent_tab (id); 1157create table child_0_10 partition of parent_tab 1158 for values from (0) to (10); 1159create table child_10_20 partition of parent_tab 1160 for values from (10) to (20); 1161create table child_20_30 partition of parent_tab 1162 for values from (20) to (30); 1163insert into parent_tab values (generate_series(0,29)); 1164create table child_30_40 partition of parent_tab 1165for values from (30) to (40) 1166 partition by range(id); 1167create table child_30_35 partition of child_30_40 1168 for values from (30) to (35); 1169create table child_35_40 partition of child_30_40 1170 for values from (35) to (40); 1171insert into parent_tab values (generate_series(30,39)); 1172 1173\dPt 1174\dPi 1175 1176\dP testpart.* 1177\dP 1178 1179\dPtn 1180\dPin 1181\dPn 1182\dPn testpart.* 1183 1184drop table parent_tab cascade; 1185 1186drop schema testpart; 1187 1188set search_path to default; 1189 1190set role to default; 1191drop role regress_partitioning_role; 1192 1193-- \d on toast table (use pg_statistic's toast table, which has a known name) 1194\d pg_toast.pg_toast_2619 1195 1196-- check printing info about access methods 1197\dA 1198\dA * 1199\dA h* 1200\dA foo 1201\dA foo bar 1202\dA+ 1203\dA+ * 1204\dA+ h* 1205\dA+ foo 1206\dAc brin pg*.oid* 1207\dAf spgist 1208\dAf btree int4 1209\dAo+ btree float_ops 1210\dAo * pg_catalog.jsonb_path_ops 1211\dAp+ btree float_ops 1212\dAp * pg_catalog.uuid_ops 1213