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-- \gexec 80 81create temporary table gexec_test(a int, b text, c date, d float); 82select format('create index on gexec_test(%I)', attname) 83from pg_attribute 84where attrelid = 'gexec_test'::regclass and attnum > 0 85order by attnum 86\gexec 87 88-- \gexec should work in FETCH_COUNT mode too 89-- (though the fetch limit applies to the executed queries not the meta query) 90\set FETCH_COUNT 1 91 92select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' 93union all 94select 'drop table gexec_test', NULL 95union all 96select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over' 97\gexec 98 99\unset FETCH_COUNT 100 101-- show all pset options 102\pset 103 104-- test multi-line headers, wrapping, and newline indicators 105prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab 106 107c", array_to_string(array_agg(repeat('y',20-2*n)),E'\n') as "a 108bc" from generate_series(1,10) as n(n) group by n>1 order by n>1; 109 110\pset linestyle ascii 111 112\pset expanded off 113\pset columns 40 114 115\pset border 0 116\pset format unaligned 117execute q; 118\pset format aligned 119execute q; 120\pset format wrapped 121execute q; 122 123\pset border 1 124\pset format unaligned 125execute q; 126\pset format aligned 127execute q; 128\pset format wrapped 129execute q; 130 131\pset border 2 132\pset format unaligned 133execute q; 134\pset format aligned 135execute q; 136\pset format wrapped 137execute q; 138 139\pset expanded on 140\pset columns 20 141 142\pset border 0 143\pset format unaligned 144execute q; 145\pset format aligned 146execute q; 147\pset format wrapped 148execute q; 149 150\pset border 1 151\pset format unaligned 152execute q; 153\pset format aligned 154execute q; 155\pset format wrapped 156execute q; 157 158\pset border 2 159\pset format unaligned 160execute q; 161\pset format aligned 162execute q; 163\pset format wrapped 164execute q; 165 166\pset linestyle old-ascii 167 168\pset expanded off 169\pset columns 40 170 171\pset border 0 172\pset format unaligned 173execute q; 174\pset format aligned 175execute q; 176\pset format wrapped 177execute q; 178 179\pset border 1 180\pset format unaligned 181execute q; 182\pset format aligned 183execute q; 184\pset format wrapped 185execute q; 186 187\pset border 2 188\pset format unaligned 189execute q; 190\pset format aligned 191execute q; 192\pset format wrapped 193execute q; 194 195\pset expanded on 196\pset columns 20 197 198\pset border 0 199\pset format unaligned 200execute q; 201\pset format aligned 202execute q; 203\pset format wrapped 204execute q; 205 206\pset border 1 207\pset format unaligned 208execute q; 209\pset format aligned 210execute q; 211\pset format wrapped 212execute q; 213 214\pset border 2 215\pset format unaligned 216execute q; 217\pset format aligned 218execute q; 219\pset format wrapped 220execute q; 221 222deallocate q; 223 224-- test single-line header and data 225prepare q as select repeat('x',2*n) as "0123456789abcdef", repeat('y',20-2*n) as "0123456789" from generate_series(1,10) as n; 226 227\pset linestyle ascii 228 229\pset expanded off 230\pset columns 40 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 256\pset expanded on 257\pset columns 30 258 259\pset border 0 260\pset format unaligned 261execute q; 262\pset format aligned 263execute q; 264\pset format wrapped 265execute q; 266 267\pset border 1 268\pset format unaligned 269execute q; 270\pset format aligned 271execute q; 272\pset format wrapped 273execute q; 274 275\pset border 2 276\pset format unaligned 277execute q; 278\pset format aligned 279execute q; 280\pset format wrapped 281execute q; 282 283\pset expanded on 284\pset columns 20 285 286\pset border 0 287\pset format unaligned 288execute q; 289\pset format aligned 290execute q; 291\pset format wrapped 292execute q; 293 294\pset border 1 295\pset format unaligned 296execute q; 297\pset format aligned 298execute q; 299\pset format wrapped 300execute q; 301 302\pset border 2 303\pset format unaligned 304execute q; 305\pset format aligned 306execute q; 307\pset format wrapped 308execute q; 309 310\pset linestyle old-ascii 311 312\pset expanded off 313\pset columns 40 314 315\pset border 0 316\pset format unaligned 317execute q; 318\pset format aligned 319execute q; 320\pset format wrapped 321execute q; 322 323\pset border 1 324\pset format unaligned 325execute q; 326\pset format aligned 327execute q; 328\pset format wrapped 329execute q; 330 331\pset border 2 332\pset format unaligned 333execute q; 334\pset format aligned 335execute q; 336\pset format wrapped 337execute q; 338 339\pset expanded on 340 341\pset border 0 342\pset format unaligned 343execute q; 344\pset format aligned 345execute q; 346\pset format wrapped 347execute q; 348 349\pset border 1 350\pset format unaligned 351execute q; 352\pset format aligned 353execute q; 354\pset format wrapped 355execute q; 356 357\pset border 2 358\pset format unaligned 359execute q; 360\pset format aligned 361execute q; 362\pset format wrapped 363execute q; 364 365deallocate q; 366 367\pset linestyle ascii 368 369prepare q as select ' | = | lkjsafi\\/ /oeu rio)(!@&*#)*(!&@*) \ (&' as " | -- | 012345678 9abc def!*@#&!@(*&*~~_+-=\ \", '11' as "0123456789", 11 as int from generate_series(1,10) as n; 370 371\pset format asciidoc 372\pset expanded off 373\pset border 0 374execute q; 375 376\pset border 1 377execute q; 378 379\pset border 2 380execute q; 381 382\pset expanded on 383\pset border 0 384execute q; 385 386\pset border 1 387execute q; 388 389\pset border 2 390execute q; 391 392deallocate q; 393 394\pset format aligned 395\pset expanded off 396\pset border 1 397 398-- tests for \if ... \endif 399 400\if true 401 select 'okay'; 402 select 'still okay'; 403\else 404 not okay; 405 still not okay 406\endif 407 408-- at this point query buffer should still have last valid line 409\g 410 411-- \if should work okay on part of a query 412select 413 \if true 414 42 415 \else 416 (bogus 417 \endif 418 forty_two; 419 420select \if false \\ (bogus \else \\ 42 \endif \\ forty_two; 421 422-- test a large nested if using a variety of true-equivalents 423\if true 424 \if 1 425 \if yes 426 \if on 427 \echo 'all true' 428 \else 429 \echo 'should not print #1-1' 430 \endif 431 \else 432 \echo 'should not print #1-2' 433 \endif 434 \else 435 \echo 'should not print #1-3' 436 \endif 437\else 438 \echo 'should not print #1-4' 439\endif 440 441-- test a variety of false-equivalents in an if/elif/else structure 442\if false 443 \echo 'should not print #2-1' 444\elif 0 445 \echo 'should not print #2-2' 446\elif no 447 \echo 'should not print #2-3' 448\elif off 449 \echo 'should not print #2-4' 450\else 451 \echo 'all false' 452\endif 453 454-- test simple true-then-else 455\if true 456 \echo 'first thing true' 457\else 458 \echo 'should not print #3-1' 459\endif 460 461-- test simple false-true-else 462\if false 463 \echo 'should not print #4-1' 464\elif true 465 \echo 'second thing true' 466\else 467 \echo 'should not print #5-1' 468\endif 469 470-- invalid boolean expressions are false 471\if invalid boolean expression 472 \echo 'will not print #6-1' 473\else 474 \echo 'will print anyway #6-2' 475\endif 476 477-- test un-matched endif 478\endif 479 480-- test un-matched else 481\else 482 483-- test un-matched elif 484\elif 485 486-- test double-else error 487\if true 488\else 489\else 490\endif 491 492-- test elif out-of-order 493\if false 494\else 495\elif 496\endif 497 498-- test if-endif matching in a false branch 499\if false 500 \if false 501 \echo 'should not print #7-1' 502 \else 503 \echo 'should not print #7-2' 504 \endif 505 \echo 'should not print #7-3' 506\else 507 \echo 'should print #7-4' 508\endif 509 510-- show that vars and backticks are not expanded when ignoring extra args 511\set foo bar 512\echo :foo :'foo' :"foo" 513\pset fieldsep | `nosuchcommand` :foo :'foo' :"foo" 514 515-- show that vars and backticks are not expanded and commands are ignored 516-- when in a false if-branch 517\set try_to_quit '\\q' 518\if false 519 :try_to_quit 520 \echo `nosuchcommand` :foo :'foo' :"foo" 521 \pset fieldsep | `nosuchcommand` :foo :'foo' :"foo" 522 \a \C arg1 \c arg1 arg2 arg3 arg4 \cd arg1 \conninfo 523 \copy arg1 arg2 arg3 arg4 arg5 arg6 524 \copyright \dt arg1 \e arg1 arg2 525 \ef whole_line 526 \ev whole_line 527 \echo arg1 arg2 arg3 arg4 arg5 \echo arg1 \encoding arg1 \errverbose 528 \g arg1 \gx arg1 \gexec \h \html \i arg1 \ir arg1 \l arg1 \lo arg1 arg2 529 \o arg1 \p \password arg1 \prompt arg1 arg2 \pset arg1 arg2 \q 530 \reset \s arg1 \set arg1 arg2 arg3 arg4 arg5 arg6 arg7 \setenv arg1 arg2 531 \sf whole_line 532 \sv whole_line 533 \t arg1 \T arg1 \timing arg1 \unset arg1 \w arg1 \watch arg1 \x arg1 534 -- \else here is eaten as part of OT_FILEPIPE argument 535 \w |/no/such/file \else 536 -- \endif here is eaten as part of whole-line argument 537 \! whole_line \endif 538\else 539 \echo 'should print #8-1' 540\endif 541 542-- SHOW_CONTEXT 543 544\set SHOW_CONTEXT never 545do $$ 546begin 547 raise notice 'foo'; 548 raise exception 'bar'; 549end $$; 550 551\set SHOW_CONTEXT errors 552do $$ 553begin 554 raise notice 'foo'; 555 raise exception 'bar'; 556end $$; 557 558\set SHOW_CONTEXT always 559do $$ 560begin 561 raise notice 'foo'; 562 raise exception 'bar'; 563end $$; 564 565-- test printing and clearing the query buffer 566SELECT 1; 567\p 568SELECT 2 \r 569\p 570SELECT 3 \p 571UNION SELECT 4 \p 572UNION SELECT 5 573ORDER BY 1; 574\r 575\p 576