1-- 2-- Tests for psql features that aren't closely connected to any 3-- specific server features 4-- 5 6-- \gset 7 8select 10 as test01, 20 as test02, 'Hello' as test03 \gset pref01_ 9 10\echo :pref01_test01 :pref01_test02 :pref01_test03 11 12-- should fail: bad variable name 13select 10 as "bad name" 14\gset 15 16select 'terse' as "OSITY", 'ok' as _foo \gset VERB 17\echo :VERB_foo :VERBOSITY 18 19-- multiple backslash commands in one line 20select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x 21select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y 22select 5 as x, 6 as y \gset pref01_ \\ \g \echo :pref01_x :pref01_y 23select 7 as x, 8 as y \g \gset pref01_ \echo :pref01_x :pref01_y 24 25-- NULL should unset the variable 26\set var2 xyz 27select 1 as var1, NULL as var2, 3 as var3 \gset 28\echo :var1 :var2 :var3 29 30-- \gset requires just one tuple 31select 10 as test01, 20 as test02 from generate_series(1,3) \gset 32select 10 as test01, 20 as test02 from generate_series(1,0) \gset 33 34-- \gset should work in FETCH_COUNT mode too 35\set FETCH_COUNT 1 36 37select 1 as x, 2 as y \gset pref01_ \\ \echo :pref01_x 38select 3 as x, 4 as y \gset pref01_ \echo :pref01_x \echo :pref01_y 39select 10 as test01, 20 as test02 from generate_series(1,3) \gset 40select 10 as test01, 20 as test02 from generate_series(1,0) \gset 41 42\unset FETCH_COUNT 43 44-- \gexec 45 46create temporary table gexec_test(a int, b text, c date, d float); 47select format('create index on gexec_test(%I)', attname) 48from pg_attribute 49where attrelid = 'gexec_test'::regclass and attnum > 0 50order by attnum 51\gexec 52 53-- \gexec should work in FETCH_COUNT mode too 54-- (though the fetch limit applies to the executed queries not the meta query) 55\set FETCH_COUNT 1 56 57select 'select 1 as ones', 'select x.y, x.y*2 as double from generate_series(1,4) as x(y)' 58union all 59select 'drop table gexec_test', NULL 60union all 61select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over' 62\gexec 63 64\unset FETCH_COUNT 65 66-- show all pset options 67\pset 68 69-- test multi-line headers, wrapping, and newline indicators 70prepare q as select array_to_string(array_agg(repeat('x',2*n)),E'\n') as "ab 71 72c", array_to_string(array_agg(repeat('y',20-2*n)),E'\n') as "a 73bc" from generate_series(1,10) as n(n) group by n>1 ; 74 75\pset linestyle ascii 76 77\pset expanded off 78\pset columns 40 79 80\pset border 0 81\pset format unaligned 82execute q; 83\pset format aligned 84execute q; 85\pset format wrapped 86execute q; 87 88\pset border 1 89\pset format unaligned 90execute q; 91\pset format aligned 92execute q; 93\pset format wrapped 94execute q; 95 96\pset border 2 97\pset format unaligned 98execute q; 99\pset format aligned 100execute q; 101\pset format wrapped 102execute q; 103 104\pset expanded on 105\pset columns 20 106 107\pset border 0 108\pset format unaligned 109execute q; 110\pset format aligned 111execute q; 112\pset format wrapped 113execute q; 114 115\pset border 1 116\pset format unaligned 117execute q; 118\pset format aligned 119execute q; 120\pset format wrapped 121execute q; 122 123\pset border 2 124\pset format unaligned 125execute q; 126\pset format aligned 127execute q; 128\pset format wrapped 129execute q; 130 131\pset linestyle old-ascii 132 133\pset expanded off 134\pset columns 40 135 136\pset border 0 137\pset format unaligned 138execute q; 139\pset format aligned 140execute q; 141\pset format wrapped 142execute q; 143 144\pset border 1 145\pset format unaligned 146execute q; 147\pset format aligned 148execute q; 149\pset format wrapped 150execute q; 151 152\pset border 2 153\pset format unaligned 154execute q; 155\pset format aligned 156execute q; 157\pset format wrapped 158execute q; 159 160\pset expanded on 161\pset columns 20 162 163\pset border 0 164\pset format unaligned 165execute q; 166\pset format aligned 167execute q; 168\pset format wrapped 169execute q; 170 171\pset border 1 172\pset format unaligned 173execute q; 174\pset format aligned 175execute q; 176\pset format wrapped 177execute q; 178 179\pset border 2 180\pset format unaligned 181execute q; 182\pset format aligned 183execute q; 184\pset format wrapped 185execute q; 186 187deallocate q; 188 189-- test single-line header and data 190prepare q as select repeat('x',2*n) as "0123456789abcdef", repeat('y',20-2*n) as "0123456789" from generate_series(1,10) as n; 191 192\pset linestyle ascii 193 194\pset expanded off 195\pset columns 40 196 197\pset border 0 198\pset format unaligned 199execute q; 200\pset format aligned 201execute q; 202\pset format wrapped 203execute q; 204 205\pset border 1 206\pset format unaligned 207execute q; 208\pset format aligned 209execute q; 210\pset format wrapped 211execute q; 212 213\pset border 2 214\pset format unaligned 215execute q; 216\pset format aligned 217execute q; 218\pset format wrapped 219execute q; 220 221\pset expanded on 222\pset columns 30 223 224\pset border 0 225\pset format unaligned 226execute q; 227\pset format aligned 228execute q; 229\pset format wrapped 230execute q; 231 232\pset border 1 233\pset format unaligned 234execute q; 235\pset format aligned 236execute q; 237\pset format wrapped 238execute q; 239 240\pset border 2 241\pset format unaligned 242execute q; 243\pset format aligned 244execute q; 245\pset format wrapped 246execute q; 247 248\pset expanded on 249\pset columns 20 250 251\pset border 0 252\pset format unaligned 253execute q; 254\pset format aligned 255execute q; 256\pset format wrapped 257execute q; 258 259\pset border 1 260\pset format unaligned 261execute q; 262\pset format aligned 263execute q; 264\pset format wrapped 265execute q; 266 267\pset border 2 268\pset format unaligned 269execute q; 270\pset format aligned 271execute q; 272\pset format wrapped 273execute q; 274 275\pset linestyle old-ascii 276 277\pset expanded off 278\pset columns 40 279 280\pset border 0 281\pset format unaligned 282execute q; 283\pset format aligned 284execute q; 285\pset format wrapped 286execute q; 287 288\pset border 1 289\pset format unaligned 290execute q; 291\pset format aligned 292execute q; 293\pset format wrapped 294execute q; 295 296\pset border 2 297\pset format unaligned 298execute q; 299\pset format aligned 300execute q; 301\pset format wrapped 302execute q; 303 304\pset expanded on 305 306\pset border 0 307\pset format unaligned 308execute q; 309\pset format aligned 310execute q; 311\pset format wrapped 312execute q; 313 314\pset border 1 315\pset format unaligned 316execute q; 317\pset format aligned 318execute q; 319\pset format wrapped 320execute q; 321 322\pset border 2 323\pset format unaligned 324execute q; 325\pset format aligned 326execute q; 327\pset format wrapped 328execute q; 329 330deallocate q; 331 332\pset linestyle ascii 333 334prepare q as select ' | = | lkjsafi\\/ /oeu rio)(!@&*#)*(!&@*) \ (&' as " | -- | 012345678 9abc def!*@#&!@(*&*~~_+-=\ \", '11' as "0123456789", 11 as int from generate_series(1,10) as n; 335 336\pset format asciidoc 337\pset expanded off 338\pset border 0 339execute q; 340 341\pset border 1 342execute q; 343 344\pset border 2 345execute q; 346 347\pset expanded on 348\pset border 0 349execute q; 350 351\pset border 1 352execute q; 353 354\pset border 2 355execute q; 356 357deallocate q; 358 359\pset format aligned 360\pset expanded off 361\pset border 1 362 363-- SHOW_CONTEXT 364 365\set SHOW_CONTEXT never 366do $$ 367begin 368 raise notice 'foo'; 369 raise exception 'bar'; 370end $$; 371 372\set SHOW_CONTEXT errors 373do $$ 374begin 375 raise notice 'foo'; 376 raise exception 'bar'; 377end $$; 378 379\set SHOW_CONTEXT always 380do $$ 381begin 382 raise notice 'foo'; 383 raise exception 'bar'; 384end $$; 385