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 51select 97 as "EOF", 'ok' as _foo \gset IGNORE 52\echo :IGNORE_foo :IGNOREEOF 53 54-- multiple backslash commands in one line 55select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x 56select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y 57select 5 as x, 6 as y \gset pref01_ \\ \g \echo :pref01_x :pref01_y 58select 7 as x, 8 as y \g \gset pref01_ \echo :pref01_x :pref01_y 59 60-- NULL should unset the variable 61\set var2 xyz 62select 1 as var1, NULL as var2, 3 as var3 \gset 63\echo :var1 :var2 :var3 64 65-- \gset requires just one tuple 66select 10 as test01, 20 as test02 from generate_series(1,3) \gset 67select 10 as test01, 20 as test02 from generate_series(1,0) \gset 68 69-- \gset should work in FETCH_COUNT mode too 70\set FETCH_COUNT 1 71 72select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x 73select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y 74select 10 as test01, 20 as test02 from generate_series(1,3) \gset 75select 10 as test01, 20 as test02 from generate_series(1,0) \gset 76 77\unset FETCH_COUNT 78 79-- \gdesc 80 81SELECT 82 NULL AS zero, 83 1 AS one, 84 2.0 AS two, 85 'three' AS three, 86 $1 AS four, 87 sin($2) as five, 88 'foo'::varchar(4) as six, 89 CURRENT_DATE AS now 90\gdesc 91 92-- should work with tuple-returning utilities, such as EXECUTE 93PREPARE test AS SELECT 1 AS first, 2 AS second; 94EXECUTE test \gdesc 95EXPLAIN EXECUTE test \gdesc 96 97-- should fail cleanly - syntax error 98SELECT 1 + \gdesc 99 100-- check behavior with empty results 101SELECT \gdesc 102CREATE TABLE bububu(a int) \gdesc 103 104-- subject command should not have executed 105TABLE bububu; -- fail 106 107-- query buffer should remain unchanged 108SELECT 1 AS x, 'Hello', 2 AS y, true AS "dirty\name" 109\gdesc 110\g 111 112-- all on one line 113SELECT 3 AS x, 'Hello', 4 AS y, true AS "dirty\name" \gdesc \g 114 115-- \gexec 116 117create temporary table gexec_test(a int, b text, c date, d float); 118select format('create index on gexec_test(%I)', attname) 119from pg_attribute 120where attrelid = 'gexec_test'::regclass and attnum > 0 121order by attnum 122\gexec 123 124-- \gexec should work in FETCH_COUNT mode too 125-- (though the fetch limit applies to the executed queries not the meta query) 126\set FETCH_COUNT 1 127 128select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' 129union all 130select 'drop table gexec_test', NULL 131union all 132select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over' 133\gexec 134 135\unset FETCH_COUNT 136 137-- show all pset options 138\pset 139 140-- test multi-line headers, wrapping, and newline indicators 141prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab 142 143c", array_to_string(array_agg(repeat('y',20-2*n)),E'\n') as "a 144bc" from generate_series(1,10) as n(n) group by n>1 order by n>1; 145 146\pset linestyle ascii 147 148\pset expanded off 149\pset columns 40 150 151\pset border 0 152\pset format unaligned 153execute q; 154\pset format aligned 155execute q; 156\pset format wrapped 157execute q; 158 159\pset border 1 160\pset format unaligned 161execute q; 162\pset format aligned 163execute q; 164\pset format wrapped 165execute q; 166 167\pset border 2 168\pset format unaligned 169execute q; 170\pset format aligned 171execute q; 172\pset format wrapped 173execute q; 174 175\pset expanded on 176\pset columns 20 177 178\pset border 0 179\pset format unaligned 180execute q; 181\pset format aligned 182execute q; 183\pset format wrapped 184execute q; 185 186\pset border 1 187\pset format unaligned 188execute q; 189\pset format aligned 190execute q; 191\pset format wrapped 192execute q; 193 194\pset border 2 195\pset format unaligned 196execute q; 197\pset format aligned 198execute q; 199\pset format wrapped 200execute q; 201 202\pset linestyle old-ascii 203 204\pset expanded off 205\pset columns 40 206 207\pset border 0 208\pset format unaligned 209execute q; 210\pset format aligned 211execute q; 212\pset format wrapped 213execute q; 214 215\pset border 1 216\pset format unaligned 217execute q; 218\pset format aligned 219execute q; 220\pset format wrapped 221execute q; 222 223\pset border 2 224\pset format unaligned 225execute q; 226\pset format aligned 227execute q; 228\pset format wrapped 229execute q; 230 231\pset expanded on 232\pset columns 20 233 234\pset border 0 235\pset format unaligned 236execute q; 237\pset format aligned 238execute q; 239\pset format wrapped 240execute q; 241 242\pset border 1 243\pset format unaligned 244execute q; 245\pset format aligned 246execute q; 247\pset format wrapped 248execute q; 249 250\pset border 2 251\pset format unaligned 252execute q; 253\pset format aligned 254execute q; 255\pset format wrapped 256execute q; 257 258deallocate q; 259 260-- test single-line header and data 261prepare q as select repeat('x',2*n) as "0123456789abcdef", repeat('y',20-2*n) as "0123456789" from generate_series(1,10) as n; 262 263\pset linestyle ascii 264 265\pset expanded off 266\pset columns 40 267 268\pset border 0 269\pset format unaligned 270execute q; 271\pset format aligned 272execute q; 273\pset format wrapped 274execute q; 275 276\pset border 1 277\pset format unaligned 278execute q; 279\pset format aligned 280execute q; 281\pset format wrapped 282execute q; 283 284\pset border 2 285\pset format unaligned 286execute q; 287\pset format aligned 288execute q; 289\pset format wrapped 290execute q; 291 292\pset expanded on 293\pset columns 30 294 295\pset border 0 296\pset format unaligned 297execute q; 298\pset format aligned 299execute q; 300\pset format wrapped 301execute q; 302 303\pset border 1 304\pset format unaligned 305execute q; 306\pset format aligned 307execute q; 308\pset format wrapped 309execute q; 310 311\pset border 2 312\pset format unaligned 313execute q; 314\pset format aligned 315execute q; 316\pset format wrapped 317execute q; 318 319\pset expanded on 320\pset columns 20 321 322\pset border 0 323\pset format unaligned 324execute q; 325\pset format aligned 326execute q; 327\pset format wrapped 328execute q; 329 330\pset border 1 331\pset format unaligned 332execute q; 333\pset format aligned 334execute q; 335\pset format wrapped 336execute q; 337 338\pset border 2 339\pset format unaligned 340execute q; 341\pset format aligned 342execute q; 343\pset format wrapped 344execute q; 345 346\pset linestyle old-ascii 347 348\pset expanded off 349\pset columns 40 350 351\pset border 0 352\pset format unaligned 353execute q; 354\pset format aligned 355execute q; 356\pset format wrapped 357execute q; 358 359\pset border 1 360\pset format unaligned 361execute q; 362\pset format aligned 363execute q; 364\pset format wrapped 365execute q; 366 367\pset border 2 368\pset format unaligned 369execute q; 370\pset format aligned 371execute q; 372\pset format wrapped 373execute q; 374 375\pset expanded on 376 377\pset border 0 378\pset format unaligned 379execute q; 380\pset format aligned 381execute q; 382\pset format wrapped 383execute q; 384 385\pset border 1 386\pset format unaligned 387execute q; 388\pset format aligned 389execute q; 390\pset format wrapped 391execute q; 392 393\pset border 2 394\pset format unaligned 395execute q; 396\pset format aligned 397execute q; 398\pset format wrapped 399execute q; 400 401deallocate q; 402 403\pset linestyle ascii 404 405prepare q as select ' | = | lkjsafi\\/ /oeu rio)(!@&*#)*(!&@*) \ (&' as " | -- | 012345678 9abc def!*@#&!@(*&*~~_+-=\ \", '11' as "0123456789", 11 as int from generate_series(1,10) as n; 406 407\pset format asciidoc 408\pset expanded off 409\pset border 0 410execute q; 411 412\pset border 1 413execute q; 414 415\pset border 2 416execute q; 417 418\pset expanded on 419\pset border 0 420execute q; 421 422\pset border 1 423execute q; 424 425\pset border 2 426execute q; 427 428deallocate q; 429 430\pset format aligned 431\pset expanded off 432\pset border 1 433 434-- tests for \if ... \endif 435 436\if true 437 select 'okay'; 438 select 'still okay'; 439\else 440 not okay; 441 still not okay 442\endif 443 444-- at this point query buffer should still have last valid line 445\g 446 447-- \if should work okay on part of a query 448select 449 \if true 450 42 451 \else 452 (bogus 453 \endif 454 forty_two; 455 456select \if false \\ (bogus \else \\ 42 \endif \\ forty_two; 457 458-- test a large nested if using a variety of true-equivalents 459\if true 460 \if 1 461 \if yes 462 \if on 463 \echo 'all true' 464 \else 465 \echo 'should not print #1-1' 466 \endif 467 \else 468 \echo 'should not print #1-2' 469 \endif 470 \else 471 \echo 'should not print #1-3' 472 \endif 473\else 474 \echo 'should not print #1-4' 475\endif 476 477-- test a variety of false-equivalents in an if/elif/else structure 478\if false 479 \echo 'should not print #2-1' 480\elif 0 481 \echo 'should not print #2-2' 482\elif no 483 \echo 'should not print #2-3' 484\elif off 485 \echo 'should not print #2-4' 486\else 487 \echo 'all false' 488\endif 489 490-- test simple true-then-else 491\if true 492 \echo 'first thing true' 493\else 494 \echo 'should not print #3-1' 495\endif 496 497-- test simple false-true-else 498\if false 499 \echo 'should not print #4-1' 500\elif true 501 \echo 'second thing true' 502\else 503 \echo 'should not print #5-1' 504\endif 505 506-- invalid boolean expressions are false 507\if invalid boolean expression 508 \echo 'will not print #6-1' 509\else 510 \echo 'will print anyway #6-2' 511\endif 512 513-- test un-matched endif 514\endif 515 516-- test un-matched else 517\else 518 519-- test un-matched elif 520\elif 521 522-- test double-else error 523\if true 524\else 525\else 526\endif 527 528-- test elif out-of-order 529\if false 530\else 531\elif 532\endif 533 534-- test if-endif matching in a false branch 535\if false 536 \if false 537 \echo 'should not print #7-1' 538 \else 539 \echo 'should not print #7-2' 540 \endif 541 \echo 'should not print #7-3' 542\else 543 \echo 'should print #7-4' 544\endif 545 546-- show that vars and backticks are not expanded when ignoring extra args 547\set foo bar 548\echo :foo :'foo' :"foo" 549\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo" 550 551-- show that vars and backticks are not expanded and commands are ignored 552-- when in a false if-branch 553\set try_to_quit '\\q' 554\if false 555 :try_to_quit 556 \echo `nosuchcommand` :foo :'foo' :"foo" 557 \pset fieldsep | `nosuchcommand` :foo :'foo' :"foo" 558 \a \C arg1 \c arg1 arg2 arg3 arg4 \cd arg1 \conninfo 559 \copy arg1 arg2 arg3 arg4 arg5 arg6 560 \copyright \dt arg1 \e arg1 arg2 561 \ef whole_line 562 \ev whole_line 563 \echo arg1 arg2 arg3 arg4 arg5 \echo arg1 \encoding arg1 \errverbose 564 \g arg1 \gx arg1 \gexec \h \html \i arg1 \ir arg1 \l arg1 \lo arg1 arg2 565 \o arg1 \p \password arg1 \prompt arg1 arg2 \pset arg1 arg2 \q 566 \reset \s arg1 \set arg1 arg2 arg3 arg4 arg5 arg6 arg7 \setenv arg1 arg2 567 \sf whole_line 568 \sv whole_line 569 \t arg1 \T arg1 \timing arg1 \unset arg1 \w arg1 \watch arg1 \x arg1 570 -- \else here is eaten as part of OT_FILEPIPE argument 571 \w |/no/such/file \else 572 -- \endif here is eaten as part of whole-line argument 573 \! whole_line \endif 574\else 575 \echo 'should print #8-1' 576\endif 577 578-- :{?...} defined variable test 579\set i 1 580\if :{?i} 581 \echo '#9-1 ok, variable i is defined' 582\else 583 \echo 'should not print #9-2' 584\endif 585 586\if :{?no_such_variable} 587 \echo 'should not print #10-1' 588\else 589 \echo '#10-2 ok, variable no_such_variable is not defined' 590\endif 591 592SELECT :{?i} AS i_is_defined; 593 594SELECT NOT :{?no_such_var} AS no_such_var_is_not_defined; 595 596-- SHOW_CONTEXT 597 598\set SHOW_CONTEXT never 599do $$ 600begin 601 raise notice 'foo'; 602 raise exception 'bar'; 603end $$; 604 605\set SHOW_CONTEXT errors 606do $$ 607begin 608 raise notice 'foo'; 609 raise exception 'bar'; 610end $$; 611 612\set SHOW_CONTEXT always 613do $$ 614begin 615 raise notice 'foo'; 616 raise exception 'bar'; 617end $$; 618 619-- test printing and clearing the query buffer 620SELECT 1; 621\p 622SELECT 2 \r 623\p 624SELECT 3 \p 625UNION SELECT 4 \p 626UNION SELECT 5 627ORDER BY 1; 628\r 629\p 630 631-- tests for special result variables 632 633-- working query, 2 rows selected 634SELECT 1 AS stuff UNION SELECT 2; 635\echo 'error:' :ERROR 636\echo 'error code:' :SQLSTATE 637\echo 'number of rows:' :ROW_COUNT 638 639-- syntax error 640SELECT 1 UNION; 641\echo 'error:' :ERROR 642\echo 'error code:' :SQLSTATE 643\echo 'number of rows:' :ROW_COUNT 644\echo 'last error message:' :LAST_ERROR_MESSAGE 645\echo 'last error code:' :LAST_ERROR_SQLSTATE 646 647-- empty query 648; 649\echo 'error:' :ERROR 650\echo 'error code:' :SQLSTATE 651\echo 'number of rows:' :ROW_COUNT 652-- must have kept previous values 653\echo 'last error message:' :LAST_ERROR_MESSAGE 654\echo 'last error code:' :LAST_ERROR_SQLSTATE 655 656-- other query error 657DROP TABLE this_table_does_not_exist; 658\echo 'error:' :ERROR 659\echo 'error code:' :SQLSTATE 660\echo 'number of rows:' :ROW_COUNT 661\echo 'last error message:' :LAST_ERROR_MESSAGE 662\echo 'last error code:' :LAST_ERROR_SQLSTATE 663 664-- working \gdesc 665SELECT 3 AS three, 4 AS four \gdesc 666\echo 'error:' :ERROR 667\echo 'error code:' :SQLSTATE 668\echo 'number of rows:' :ROW_COUNT 669 670-- \gdesc with an error 671SELECT 4 AS \gdesc 672\echo 'error:' :ERROR 673\echo 'error code:' :SQLSTATE 674\echo 'number of rows:' :ROW_COUNT 675\echo 'last error message:' :LAST_ERROR_MESSAGE 676\echo 'last error code:' :LAST_ERROR_SQLSTATE 677 678-- check row count for a cursor-fetched query 679\set FETCH_COUNT 10 680select unique2 from tenk1 order by unique2 limit 19; 681\echo 'error:' :ERROR 682\echo 'error code:' :SQLSTATE 683\echo 'number of rows:' :ROW_COUNT 684 685-- cursor-fetched query with an error after the first group 686select 1/(15-unique2) from tenk1 order by unique2 limit 19; 687\echo 'error:' :ERROR 688\echo 'error code:' :SQLSTATE 689\echo 'number of rows:' :ROW_COUNT 690\echo 'last error message:' :LAST_ERROR_MESSAGE 691\echo 'last error code:' :LAST_ERROR_SQLSTATE 692 693\unset FETCH_COUNT 694