src/test/regress/sql/portals.sql

   1 --
   2 -- Cursor regression tests
   3 --
   4
   5 BEGIN;
   6
   7 DECLARE foo1 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
   8
   9 DECLARE foo2 SCROLL CURSOR FOR SELECT * FROM tenk2;
  10
  11 DECLARE foo3 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  12
  13 DECLARE foo4 SCROLL CURSOR FOR SELECT * FROM tenk2;
  14
  15 DECLARE foo5 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  16
  17 DECLARE foo6 SCROLL CURSOR FOR SELECT * FROM tenk2;
  18
  19 DECLARE foo7 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  20
  21 DECLARE foo8 SCROLL CURSOR FOR SELECT * FROM tenk2;
  22
  23 DECLARE foo9 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  24
  25 DECLARE foo10 SCROLL CURSOR FOR SELECT * FROM tenk2;
  26
  27 DECLARE foo11 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  28
  29 DECLARE foo12 SCROLL CURSOR FOR SELECT * FROM tenk2;
  30
  31 DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  32
  33 DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2;
  34
  35 DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  36
  37 DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2;
  38
  39 DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  40
  41 DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2;
  42
  43 DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  44
  45 DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2;
  46
  47 DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  48
  49 DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2;
  50
  51 DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
  52
  53 FETCH 1 in foo1;
  54
  55 FETCH 2 in foo2;
  56
  57 FETCH 3 in foo3;
  58
  59 FETCH 4 in foo4;
  60
  61 FETCH 5 in foo5;
  62
  63 FETCH 6 in foo6;
  64
  65 FETCH 7 in foo7;
  66
  67 FETCH 8 in foo8;
  68
  69 FETCH 9 in foo9;
  70
  71 FETCH 10 in foo10;
  72
  73 FETCH 11 in foo11;
  74
  75 FETCH 12 in foo12;
  76
  77 FETCH 13 in foo13;
  78
  79 FETCH 14 in foo14;
  80
  81 FETCH 15 in foo15;
  82
  83 FETCH 16 in foo16;
  84
  85 FETCH 17 in foo17;
  86
  87 FETCH 18 in foo18;
  88
  89 FETCH 19 in foo19;
  90
  91 FETCH 20 in foo20;
  92
  93 FETCH 21 in foo21;
  94
  95 FETCH 22 in foo22;
  96
  97 FETCH 23 in foo23;
  98
  99 FETCH backward 1 in foo23;
 100
 101 FETCH backward 2 in foo22;
 102
 103 FETCH backward 3 in foo21;
 104
 105 FETCH backward 4 in foo20;
 106
 107 FETCH backward 5 in foo19;
 108
 109 FETCH backward 6 in foo18;
 110
 111 FETCH backward 7 in foo17;
 112
 113 FETCH backward 8 in foo16;
 114
 115 FETCH backward 9 in foo15;
 116
 117 FETCH backward 10 in foo14;
 118
 119 FETCH backward 11 in foo13;
 120
 121 FETCH backward 12 in foo12;
 122
 123 FETCH backward 13 in foo11;
 124
 125 FETCH backward 14 in foo10;
 126
 127 FETCH backward 15 in foo9;
 128
 129 FETCH backward 16 in foo8;
 130
 131 FETCH backward 17 in foo7;
 132
 133 FETCH backward 18 in foo6;
 134
 135 FETCH backward 19 in foo5;
 136
 137 FETCH backward 20 in foo4;
 138
 139 FETCH backward 21 in foo3;
 140
 141 FETCH backward 22 in foo2;
 142
 143 FETCH backward 23 in foo1;
 144
 145 CLOSE foo1;
 146
 147 CLOSE foo2;
 148
 149 CLOSE foo3;
 150
 151 CLOSE foo4;
 152
 153 CLOSE foo5;
 154
 155 CLOSE foo6;
 156
 157 CLOSE foo7;
 158
 159 CLOSE foo8;
 160
 161 CLOSE foo9;
 162
 163 CLOSE foo10;
 164
 165 CLOSE foo11;
 166
 167 CLOSE foo12;
 168
 169 -- leave some cursors open, to test that auto-close works.
 170
 171 -- record this in the system view as well (don't query the time field there
 172 -- however)
 173 SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY 1;
 174
 175 END;
 176
 177 SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
 178
 179 --
 180 -- NO SCROLL disallows backward fetching
 181 --
 182
 183 BEGIN;
 184
 185 DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2;
 186
 187 FETCH 1 FROM foo24;
 188
 189 FETCH BACKWARD 1 FROM foo24; -- should fail
 190
 191 END;
 192
 193 --
 194 -- Cursors outside transaction blocks
 195 --
 196
 197
 198 SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
 199
 200 BEGIN;
 201
 202 DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
 203
 204 FETCH FROM foo25;
 205
 206 FETCH FROM foo25;
 207
 208 COMMIT;
 209
 210 FETCH FROM foo25;
 211
 212 FETCH BACKWARD FROM foo25;
 213
 214 FETCH ABSOLUTE -1 FROM foo25;
 215
 216 SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
 217
 218 CLOSE foo25;
 219
 220 --
 221 -- ROLLBACK should close holdable cursors
 222 --
 223
 224 BEGIN;
 225
 226 DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM tenk1 ORDER BY unique2;
 227
 228 ROLLBACK;
 229
 230 -- should fail
 231 FETCH FROM foo26;
 232
 233 --
 234 -- Parameterized DECLARE needs to insert param values into the cursor portal
 235 --
 236
 237 BEGIN;
 238
 239 CREATE FUNCTION declares_cursor(text)
 240    RETURNS void
 241    AS 'DECLARE c CURSOR FOR SELECT stringu1 FROM tenk1 WHERE stringu1 LIKE $1;'
 242    LANGUAGE SQL;
 243
 244 SELECT declares_cursor('AB%');
 245
 246 FETCH ALL FROM c;
 247
 248 ROLLBACK;
 249
 250 --
 251 -- Test behavior of both volatile and stable functions inside a cursor;
 252 -- in particular we want to see what happens during commit of a holdable
 253 -- cursor
 254 --
 255
 256 create temp table tt1(f1 int);
 257
 258 create function count_tt1_v() returns int8 as
 259 'select count(*) from tt1' language sql volatile;
 260
 261 create function count_tt1_s() returns int8 as
 262 'select count(*) from tt1' language sql stable;
 263
 264 begin;
 265
 266 insert into tt1 values(1);
 267
 268 declare c1 cursor for select count_tt1_v(), count_tt1_s();
 269
 270 insert into tt1 values(2);
 271
 272 fetch all from c1;
 273
 274 rollback;
 275
 276 begin;
 277
 278 insert into tt1 values(1);
 279
 280 declare c2 cursor with hold for select count_tt1_v(), count_tt1_s();
 281
 282 insert into tt1 values(2);
 283
 284 commit;
 285
 286 delete from tt1;
 287
 288 fetch all from c2;
 289
 290 drop function count_tt1_v();
 291 drop function count_tt1_s();
 292
 293
 294 -- Create a cursor with the BINARY option and check the pg_cursors view
 295 BEGIN;
 296 SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
 297 DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1;
 298 SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY 1;
 299 ROLLBACK;
 300
 301 -- We should not see the portal that is created internally to
 302 -- implement EXECUTE in pg_cursors
 303 PREPARE cprep AS
 304   SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
 305 EXECUTE cprep;
 306
 307 -- test CLOSE ALL;
 308 SELECT name FROM pg_cursors ORDER BY 1;
 309 CLOSE ALL;
 310 SELECT name FROM pg_cursors ORDER BY 1;
 311 BEGIN;
 312 DECLARE foo1 CURSOR WITH HOLD FOR SELECT 1;
 313 DECLARE foo2 CURSOR WITHOUT HOLD FOR SELECT 1;
 314 SELECT name FROM pg_cursors ORDER BY 1;
 315 CLOSE ALL;
 316 SELECT name FROM pg_cursors ORDER BY 1;
 317 COMMIT;
 318
 319 --
 320 -- Tests for updatable cursors
 321 --
 322
 323 CREATE TEMP TABLE uctest(f1 int, f2 text);
 324 INSERT INTO uctest VALUES (1, 'one'), (2, 'two'), (3, 'three');
 325 SELECT * FROM uctest;
 326
 327 -- Check DELETE WHERE CURRENT
 328 BEGIN;
 329 DECLARE c1 CURSOR FOR SELECT * FROM uctest;
 330 FETCH 2 FROM c1;
 331 DELETE FROM uctest WHERE CURRENT OF c1;
 332 -- should show deletion
 333 SELECT * FROM uctest;
 334 -- cursor did not move
 335 FETCH ALL FROM c1;
 336 -- cursor is insensitive
 337 MOVE BACKWARD ALL IN c1;
 338 FETCH ALL FROM c1;
 339 COMMIT;
 340 -- should still see deletion
 341 SELECT * FROM uctest;
 342
 343 -- Check UPDATE WHERE CURRENT; this time use FOR UPDATE
 344 BEGIN;
 345 DECLARE c1 CURSOR FOR SELECT * FROM uctest FOR UPDATE;
 346 FETCH c1;
 347 UPDATE uctest SET f1 = 8 WHERE CURRENT OF c1;
 348 SELECT * FROM uctest;
 349 COMMIT;
 350 SELECT * FROM uctest;
 351
 352 -- Check repeated-update and update-then-delete cases
 353 BEGIN;
 354 DECLARE c1 CURSOR FOR SELECT * FROM uctest;
 355 FETCH c1;
 356 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 357 SELECT * FROM uctest;
 358 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 359 SELECT * FROM uctest;
 360 -- insensitive cursor should not show effects of updates or deletes
 361 FETCH RELATIVE 0 FROM c1;
 362 DELETE FROM uctest WHERE CURRENT OF c1;
 363 SELECT * FROM uctest;
 364 DELETE FROM uctest WHERE CURRENT OF c1; -- no-op
 365 SELECT * FROM uctest;
 366 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op
 367 SELECT * FROM uctest;
 368 FETCH RELATIVE 0 FROM c1;
 369 ROLLBACK;
 370 SELECT * FROM uctest;
 371
 372 BEGIN;
 373 DECLARE c1 CURSOR FOR SELECT * FROM uctest FOR UPDATE;
 374 FETCH c1;
 375 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 376 SELECT * FROM uctest;
 377 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 378 SELECT * FROM uctest;
 379 DELETE FROM uctest WHERE CURRENT OF c1;
 380 SELECT * FROM uctest;
 381 DELETE FROM uctest WHERE CURRENT OF c1; -- no-op
 382 SELECT * FROM uctest;
 383 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op
 384 SELECT * FROM uctest;
 385 --- sensitive cursors can't currently scroll back, so this is an error:
 386 FETCH RELATIVE 0 FROM c1;
 387 ROLLBACK;
 388 SELECT * FROM uctest;
 389
 390 -- Check inheritance cases
 391 CREATE TEMP TABLE ucchild () inherits (uctest);
 392 INSERT INTO ucchild values(100, 'hundred');
 393 SELECT * FROM uctest;
 394
 395 BEGIN;
 396 DECLARE c1 CURSOR FOR SELECT * FROM uctest FOR UPDATE;
 397 FETCH 1 FROM c1;
 398 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 399 FETCH 1 FROM c1;
 400 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 401 FETCH 1 FROM c1;
 402 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 403 FETCH 1 FROM c1;
 404 COMMIT;
 405 SELECT * FROM uctest;
 406
 407 -- Can update from a self-join, but only if FOR UPDATE says which to use
 408 BEGIN;
 409 DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5;
 410 FETCH 1 FROM c1;
 411 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;  -- fail
 412 ROLLBACK;
 413 BEGIN;
 414 DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 FOR UPDATE;
 415 FETCH 1 FROM c1;
 416 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;  -- fail
 417 ROLLBACK;
 418 BEGIN;
 419 DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 FOR SHARE OF a;
 420 FETCH 1 FROM c1;
 421 UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1;
 422 SELECT * FROM uctest;
 423 ROLLBACK;
 424
 425 -- Check various error cases
 426
 427 DELETE FROM uctest WHERE CURRENT OF c1;  -- fail, no such cursor
 428 DECLARE cx CURSOR WITH HOLD FOR SELECT * FROM uctest;
 429 DELETE FROM uctest WHERE CURRENT OF cx;  -- fail, can't use held cursor
 430 BEGIN;
 431 DECLARE c CURSOR FOR SELECT * FROM tenk2;
 432 DELETE FROM uctest WHERE CURRENT OF c;  -- fail, cursor on wrong table
 433 ROLLBACK;
 434 BEGIN;
 435 DECLARE c CURSOR FOR SELECT * FROM tenk2 FOR SHARE;
 436 DELETE FROM uctest WHERE CURRENT OF c;  -- fail, cursor on wrong table
 437 ROLLBACK;
 438 BEGIN;
 439 DECLARE c CURSOR FOR SELECT * FROM tenk1 JOIN tenk2 USING (unique1);
 440 DELETE FROM tenk1 WHERE CURRENT OF c;  -- fail, cursor is on a join
 441 ROLLBACK;
 442 BEGIN;
 443 DECLARE c CURSOR FOR SELECT f1,count(*) FROM uctest GROUP BY f1;
 444 DELETE FROM uctest WHERE CURRENT OF c;  -- fail, cursor is on aggregation
 445 ROLLBACK;
 446 BEGIN;
 447 DECLARE c1 CURSOR FOR SELECT * FROM uctest;
 448 DELETE FROM uctest WHERE CURRENT OF c1; -- fail, no current row
 449 ROLLBACK;
 450
 451 -- WHERE CURRENT OF may someday work with views, but today is not that day.
 452 -- For now, just make sure it errors out cleanly.
 453 CREATE TEMP VIEW ucview AS SELECT * FROM uctest;
 454 CREATE RULE ucrule AS ON DELETE TO ucview DO INSTEAD
 455   DELETE FROM uctest WHERE f1 = OLD.f1;
 456 BEGIN;
 457 DECLARE c1 CURSOR FOR SELECT * FROM ucview;
 458 FETCH FROM c1;
 459 DELETE FROM ucview WHERE CURRENT OF c1; -- fail, views not supported
 460 ROLLBACK;