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