1-- 2-- MULTI_UTILITY_STATEMENTS 3-- 4-- Check that we can run utility statements with embedded SELECT statements on 5-- distributed tables. Currently we only support CREATE TABLE AS (SELECT..), 6-- DECLARE CURSOR, and COPY ... TO statements. 7SET citus.next_shard_id TO 1000000; 8CREATE TEMP TABLE lineitem_pricing_summary AS 9( 10 SELECT 11 l_returnflag, 12 l_linestatus, 13 sum(l_quantity) as sum_qty, 14 sum(l_extendedprice) as sum_base_price, 15 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, 16 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, 17 avg(l_quantity) as avg_qty, 18 avg(l_extendedprice) as avg_price, 19 avg(l_discount) as avg_disc, 20 count(*) as count_order 21 FROM 22 lineitem 23 WHERE 24 l_shipdate <= date '1998-12-01' - interval '90 days' 25 GROUP BY 26 l_returnflag, 27 l_linestatus 28 ORDER BY 29 l_returnflag, 30 l_linestatus 31); 32SELECT * FROM lineitem_pricing_summary ORDER BY l_returnflag, l_linestatus; 33 l_returnflag | l_linestatus | sum_qty | sum_base_price | sum_disc_price | sum_charge | avg_qty | avg_price | avg_disc | count_order 34--------------------------------------------------------------------- 35 A | F | 75465.00 | 113619873.63 | 107841287.0728 | 112171153.245923 | 25.6334918478260870 | 38593.707075407609 | 0.05055027173913043478 | 2944 36 N | F | 2022.00 | 3102551.45 | 2952540.7118 | 3072642.770652 | 26.6052631578947368 | 40823.045394736842 | 0.05263157894736842105 | 76 37 N | O | 149778.00 | 224706948.16 | 213634857.6854 | 222134071.929801 | 25.4594594594594595 | 38195.979629440762 | 0.04939486656467788543 | 5883 38 R | F | 73156.00 | 108937979.73 | 103516623.6698 | 107743533.784328 | 25.2175112030334367 | 37551.871675284385 | 0.04983798690106859704 | 2901 39(4 rows) 40 41-- Test we can handle joins 42CREATE TABLE shipping_priority AS 43( 44 SELECT 45 l_orderkey, 46 sum(l_extendedprice * (1 - l_discount)) as revenue, 47 o_orderdate, 48 o_shippriority 49 FROM 50 customer, 51 orders, 52 lineitem 53 WHERE 54 c_mktsegment = 'BUILDING' 55 AND c_custkey = o_custkey 56 AND l_orderkey = o_orderkey 57 AND o_orderdate < date '1995-03-15' 58 AND l_shipdate > date '1995-03-15' 59 GROUP BY 60 l_orderkey, 61 o_orderdate, 62 o_shippriority 63 ORDER BY 64 revenue DESC, 65 o_orderdate 66); 67SELECT * FROM shipping_priority; 68 l_orderkey | revenue | o_orderdate | o_shippriority 69--------------------------------------------------------------------- 70 1637 | 268170.6408 | 02-08-1995 | 0 71 9696 | 252014.5497 | 02-20-1995 | 0 72 10916 | 242749.1996 | 03-11-1995 | 0 73 450 | 221012.3165 | 03-05-1995 | 0 74 5347 | 198353.7942 | 02-22-1995 | 0 75 10691 | 112800.1020 | 03-14-1995 | 0 76 386 | 104975.2484 | 01-25-1995 | 0 77 5765 | 88222.7556 | 12-15-1994 | 0 78 4707 | 88143.7774 | 02-27-1995 | 0 79 5312 | 83750.7028 | 02-24-1995 | 0 80 5728 | 70101.6400 | 12-11-1994 | 0 81 577 | 57986.6224 | 12-19-1994 | 0 82 12706 | 16636.6368 | 11-21-1994 | 0 83 3844 | 8851.3200 | 12-29-1994 | 0 84 11073 | 7433.6295 | 12-02-1994 | 0 85 13924 | 3111.4970 | 12-20-1994 | 0 86(16 rows) 87 88DROP TABLE shipping_priority; 89-- Check COPY against distributed tables works both when specifying a 90-- query as the source, and when directly naming a table. 91COPY ( 92 SELECT 93 l_orderkey, 94 sum(l_extendedprice * (1 - l_discount)) as revenue, 95 o_orderdate, 96 o_shippriority 97 FROM 98 customer, 99 orders, 100 lineitem 101 WHERE 102 c_mktsegment = 'BUILDING' 103 AND c_custkey = o_custkey 104 AND l_orderkey = o_orderkey 105 AND o_orderdate < date '1995-03-15' 106 AND l_shipdate > date '1995-03-15' 107 GROUP BY 108 l_orderkey, 109 o_orderdate, 110 o_shippriority 111 ORDER BY 112 revenue DESC, 113 o_orderdate 114) TO stdout; 1151637 268170.6408 02-08-1995 0 1169696 252014.5497 02-20-1995 0 11710916 242749.1996 03-11-1995 0 118450 221012.3165 03-05-1995 0 1195347 198353.7942 02-22-1995 0 12010691 112800.1020 03-14-1995 0 121386 104975.2484 01-25-1995 0 1225765 88222.7556 12-15-1994 0 1234707 88143.7774 02-27-1995 0 1245312 83750.7028 02-24-1995 0 1255728 70101.6400 12-11-1994 0 126577 57986.6224 12-19-1994 0 12712706 16636.6368 11-21-1994 0 1283844 8851.3200 12-29-1994 0 12911073 7433.6295 12-02-1994 0 13013924 3111.4970 12-20-1994 0 131-- check copying to file 132-- (quiet off to force number of copied records to be displayed) 133\set QUIET off 134COPY nation TO :'dev_null'; 135COPY 25 136\set QUIET on 137-- stdout 138COPY nation TO STDOUT; 1390 ALGERIA 0 haggle. carefully final deposits detect slyly agai 1401 ARGENTINA 1 al foxes promise slyly according to the regular accounts. bold requests alon 1412 BRAZIL 1 y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special 1423 CANADA 1 eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold 1434 EGYPT 4 y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d 1445 ETHIOPIA 0 ven packages wake quickly. regu 1456 FRANCE 3 refully final requests. regular, ironi 1467 GERMANY 3 l platelets. regular accounts x-ray: unusual, regular acco 1478 INDIA 2 ss excuses cajole slyly across the packages. deposits print aroun 1489 INDONESIA 2 slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull 14910 IRAN 4 efully alongside of the slyly final dependencies. 15011 IRAQ 4 nic deposits boost atop the quickly final requests? quickly regula 15112 JAPAN 2 ously. final, express gifts cajole a 15213 JORDAN 4 ic deposits are blithely about the carefully regular pa 15314 KENYA 0 pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t 15415 MOROCCO 0 rns. blithely bold courts among the closely regular packages use furiously bold platelets? 15516 MOZAMBIQUE 0 s. ironic, unusual asymptotes wake blithely r 15617 PERU 1 platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun 15718 CHINA 2 c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos 15819 ROMANIA 3 ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account 15920 SAUDI ARABIA 4 ts. silent requests haggle. closely express packages sleep across the blithely 16021 VIETNAM 2 hely enticingly express accounts. even, final 16122 RUSSIA 3 requests against the platelets use never according to the quickly regular pint 16223 UNITED KINGDOM 3 eans boost carefully special requests. accounts are. carefull 16324 UNITED STATES 1 y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be 164-- ensure individual cols can be copied out, too 165COPY nation(n_name) TO STDOUT; 166ALGERIA 167ARGENTINA 168BRAZIL 169CANADA 170EGYPT 171ETHIOPIA 172FRANCE 173GERMANY 174INDIA 175INDONESIA 176IRAN 177IRAQ 178JAPAN 179JORDAN 180KENYA 181MOROCCO 182MOZAMBIQUE 183PERU 184CHINA 185ROMANIA 186SAUDI ARABIA 187VIETNAM 188RUSSIA 189UNITED KINGDOM 190UNITED STATES 191-- Test that we can create on-commit drop tables, along with changing column names 192BEGIN; 193CREATE TEMP TABLE customer_few (customer_key) ON COMMIT DROP AS 194 (SELECT * FROM customer WHERE c_nationkey = 1 ORDER BY c_custkey LIMIT 10); 195SELECT customer_key, c_name, c_address 196 FROM customer_few ORDER BY customer_key LIMIT 5; 197 customer_key | c_name | c_address 198--------------------------------------------------------------------- 199 3 | Customer#000000003 | MG9kdTD2WBHm 200 14 | Customer#000000014 | KXkletMlL2JQEA 201 30 | Customer#000000030 | nJDsELGAavU63Jl0c5NKsKfL8rIJQQkQnYL2QJY 202 59 | Customer#000000059 | zLOCP0wh92OtBihgspOGl4 203 106 | Customer#000000106 | xGCOEAUjUNG 204(5 rows) 205 206COMMIT; 207SELECT customer_key, c_name, c_address 208 FROM customer_few ORDER BY customer_key LIMIT 5; 209ERROR: relation "customer_few" does not exist 210-- Test DECLARE CURSOR .. WITH HOLD without parameters that calls ReScan on the top-level CustomScan 211CREATE TABLE cursor_me (x int, y int); 212SELECT create_distributed_table('cursor_me', 'x'); 213 create_distributed_table 214--------------------------------------------------------------------- 215 216(1 row) 217 218INSERT INTO cursor_me SELECT s/10, s FROM generate_series(1, 100) s; 219DECLARE holdCursor CURSOR WITH HOLD FOR 220 SELECT * FROM cursor_me WHERE x = 1 ORDER BY y; 221FETCH NEXT FROM holdCursor; 222 x | y 223--------------------------------------------------------------------- 224 1 | 10 225(1 row) 226 227FETCH FORWARD 3 FROM holdCursor; 228 x | y 229--------------------------------------------------------------------- 230 1 | 11 231 1 | 12 232 1 | 13 233(3 rows) 234 235FETCH LAST FROM holdCursor; 236 x | y 237--------------------------------------------------------------------- 238 1 | 19 239(1 row) 240 241FETCH BACKWARD 3 FROM holdCursor; 242 x | y 243--------------------------------------------------------------------- 244 1 | 18 245 1 | 17 246 1 | 16 247(3 rows) 248 249FETCH FORWARD 3 FROM holdCursor; 250 x | y 251--------------------------------------------------------------------- 252 1 | 17 253 1 | 18 254 1 | 19 255(3 rows) 256 257CLOSE holdCursor; 258-- Test DECLARE CURSOR .. WITH HOLD with parameter 259CREATE OR REPLACE FUNCTION declares_cursor(p int) 260RETURNS void AS $$ 261 DECLARE c CURSOR WITH HOLD FOR SELECT * FROM cursor_me WHERE x = $1; 262$$ LANGUAGE SQL; 263SELECT declares_cursor(5); 264 declares_cursor 265--------------------------------------------------------------------- 266 267(1 row) 268 269-- Test DECLARE CURSOR .. WITH HOLD without parameter 270CREATE OR REPLACE FUNCTION declares_cursor_2() 271RETURNS void AS $$ 272 DECLARE c2 CURSOR WITH HOLD FOR SELECT * FROM cursor_me; 273$$ LANGUAGE SQL; 274SELECT declares_cursor_2(); 275 declares_cursor_2 276--------------------------------------------------------------------- 277 278(1 row) 279 280-- Test DECLARE CURSOR .. WITH HOLD with parameter on non-dist key 281CREATE OR REPLACE FUNCTION declares_cursor_3(p int) 282RETURNS void AS $$ 283 DECLARE c3 CURSOR WITH HOLD FOR SELECT * FROM cursor_me WHERE y = $1; 284$$ LANGUAGE SQL; 285SELECT declares_cursor_3(19); 286ERROR: Cursors for queries on distributed tables with parameters are currently unsupported 287-- Test DECLARE CURSOR .. WITH HOLD with parameter on dist key, but not fast-path planner 288CREATE OR REPLACE FUNCTION declares_cursor_4(p int) 289RETURNS void AS $$ 290 DECLARE c4 CURSOR WITH HOLD FOR SELECT *, (SELECT 1) FROM cursor_me WHERE x = $1; 291$$ LANGUAGE SQL; 292SELECT declares_cursor_4(19); 293ERROR: Cursors for queries on distributed tables with parameters are currently unsupported 294CREATE OR REPLACE FUNCTION cursor_plpgsql(p int) 295RETURNS SETOF int AS $$ 296DECLARE 297 val int; 298 my_cursor CURSOR (a INTEGER) FOR SELECT y FROM cursor_me WHERE x = $1 ORDER BY y; 299BEGIN 300 -- Open the cursor 301 OPEN my_cursor(p); 302 303 LOOP 304 FETCH my_cursor INTO val; 305 EXIT WHEN NOT FOUND; 306 307 RETURN NEXT val; 308 END LOOP; 309 310 -- Close the cursor 311 CLOSE my_cursor; 312END; $$ 313LANGUAGE plpgsql; 314SELECT cursor_plpgsql(4); 315 cursor_plpgsql 316--------------------------------------------------------------------- 317 40 318 41 319 42 320 43 321 44 322 45 323 46 324 47 325 48 326 49 327(10 rows) 328 329DROP FUNCTION declares_cursor(int); 330DROP FUNCTION cursor_plpgsql(int); 331DROP TABLE cursor_me; 332-- Test DECLARE CURSOR statement with SCROLL 333DECLARE holdCursor SCROLL CURSOR WITH HOLD FOR 334 SELECT l_orderkey, l_linenumber, l_quantity, l_discount 335 FROM lineitem 336 ORDER BY l_orderkey, l_linenumber; 337FETCH NEXT FROM holdCursor; 338 l_orderkey | l_linenumber | l_quantity | l_discount 339--------------------------------------------------------------------- 340 1 | 1 | 17.00 | 0.04 341(1 row) 342 343FETCH FORWARD 5 FROM holdCursor; 344 l_orderkey | l_linenumber | l_quantity | l_discount 345--------------------------------------------------------------------- 346 1 | 2 | 36.00 | 0.09 347 1 | 3 | 8.00 | 0.10 348 1 | 4 | 28.00 | 0.09 349 1 | 5 | 24.00 | 0.10 350 1 | 6 | 32.00 | 0.07 351(5 rows) 352 353FETCH LAST FROM holdCursor; 354 l_orderkey | l_linenumber | l_quantity | l_discount 355--------------------------------------------------------------------- 356 14947 | 2 | 29.00 | 0.04 357(1 row) 358 359FETCH BACKWARD 5 FROM holdCursor; 360 l_orderkey | l_linenumber | l_quantity | l_discount 361--------------------------------------------------------------------- 362 14947 | 1 | 14.00 | 0.09 363 14946 | 2 | 37.00 | 0.01 364 14946 | 1 | 38.00 | 0.00 365 14945 | 6 | 37.00 | 0.05 366 14945 | 5 | 44.00 | 0.08 367(5 rows) 368 369-- Test WITHOUT HOLD cursors inside transactions 370BEGIN; 371DECLARE noHoldCursor SCROLL CURSOR FOR 372 SELECT l_orderkey, l_linenumber, l_quantity, l_discount 373 FROM lineitem 374 ORDER BY l_orderkey, l_linenumber; 375FETCH ABSOLUTE 5 FROM noHoldCursor; 376 l_orderkey | l_linenumber | l_quantity | l_discount 377--------------------------------------------------------------------- 378 1 | 5 | 24.00 | 0.10 379(1 row) 380 381FETCH BACKWARD noHoldCursor; 382 l_orderkey | l_linenumber | l_quantity | l_discount 383--------------------------------------------------------------------- 384 1 | 4 | 28.00 | 0.09 385(1 row) 386 387COMMIT; 388FETCH ABSOLUTE 5 FROM noHoldCursor; 389ERROR: cursor "noholdcursor" does not exist 390-- Test we don't throw an error for DROP IF EXISTS 391DROP DATABASE IF EXISTS not_existing_database; 392NOTICE: database "not_existing_database" does not exist, skipping 393DROP TABLE IF EXISTS not_existing_table; 394NOTICE: table "not_existing_table" does not exist, skipping 395DROP SCHEMA IF EXISTS not_existing_schema; 396NOTICE: schema "not_existing_schema" does not exist, skipping 397