src/test/regress/expected/rangefuncs.out

   1 SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
   2        name        | setting
   3 -------------------+---------
   4  enable_bitmapscan | on
   5  enable_hashagg    | on
   6  enable_hashjoin   | on
   7  enable_indexscan  | on
   8  enable_mergejoin  | on
   9  enable_nestloop   | on
  10  enable_seqscan    | on
  11  enable_sort       | on
  12  enable_tidscan    | on
  13 (9 rows)
  14
  15 CREATE TABLE foo2(fooid int, f2 int);
  16 INSERT INTO foo2 VALUES(1, 11);
  17 INSERT INTO foo2 VALUES(2, 22);
  18 INSERT INTO foo2 VALUES(1, 111);
  19 CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
  20 -- supposed to fail with ERROR
  21 select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
  22 ERROR:  function expression in FROM cannot refer to other relations of same query level
  23 LINE 1: select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
  24                                  ^
  25 -- function in subselect
  26 select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
  27  fooid | f2
  28 -------+-----
  29      1 |  11
  30      1 | 111
  31      2 |  22
  32 (3 rows)
  33
  34 -- function in subselect
  35 select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
  36  fooid | f2
  37 -------+-----
  38      1 |  11
  39      1 | 111
  40 (2 rows)
  41
  42 -- function in subselect
  43 select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
  44  fooid | f2
  45 -------+-----
  46      1 |  11
  47      1 | 111
  48 (2 rows)
  49
  50 -- nested functions
  51 select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
  52  fooid | f2
  53 -------+-----
  54      1 |  11
  55      1 | 111
  56 (2 rows)
  57
  58 CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
  59 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
  60 INSERT INTO foo VALUES(1,1,'Joe');
  61 INSERT INTO foo VALUES(1,2,'Ed');
  62 INSERT INTO foo VALUES(2,1,'Mary');
  63 -- sql, proretset = f, prorettype = b
  64 CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
  65 SELECT * FROM getfoo(1) AS t1;
  66  t1
  67 ----
  68   1
  69 (1 row)
  70
  71 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
  72 SELECT * FROM vw_getfoo;
  73  getfoo
  74 --------
  75       1
  76 (1 row)
  77
  78 -- sql, proretset = t, prorettype = b
  79 DROP VIEW vw_getfoo;
  80 DROP FUNCTION getfoo(int);
  81 CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
  82 SELECT * FROM getfoo(1) AS t1;
  83  t1
  84 ----
  85   1
  86   1
  87 (2 rows)
  88
  89 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
  90 SELECT * FROM vw_getfoo;
  91  getfoo
  92 --------
  93       1
  94       1
  95 (2 rows)
  96
  97 -- sql, proretset = t, prorettype = b
  98 DROP VIEW vw_getfoo;
  99 DROP FUNCTION getfoo(int);
 100 CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 101 SELECT * FROM getfoo(1) AS t1;
 102  t1
 103 -----
 104  Joe
 105  Ed
 106 (2 rows)
 107
 108 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 109 SELECT * FROM vw_getfoo;
 110  getfoo
 111 --------
 112  Joe
 113  Ed
 114 (2 rows)
 115
 116 -- sql, proretset = f, prorettype = c
 117 DROP VIEW vw_getfoo;
 118 DROP FUNCTION getfoo(int);
 119 CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 120 SELECT * FROM getfoo(1) AS t1;
 121  fooid | foosubid | fooname
 122 -------+----------+---------
 123      1 |        1 | Joe
 124 (1 row)
 125
 126 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 127 SELECT * FROM vw_getfoo;
 128  fooid | foosubid | fooname
 129 -------+----------+---------
 130      1 |        1 | Joe
 131 (1 row)
 132
 133 -- sql, proretset = t, prorettype = c
 134 DROP VIEW vw_getfoo;
 135 DROP FUNCTION getfoo(int);
 136 CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 137 SELECT * FROM getfoo(1) AS t1;
 138  fooid | foosubid | fooname
 139 -------+----------+---------
 140      1 |        1 | Joe
 141      1 |        2 | Ed
 142 (2 rows)
 143
 144 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 145 SELECT * FROM vw_getfoo;
 146  fooid | foosubid | fooname
 147 -------+----------+---------
 148      1 |        1 | Joe
 149      1 |        2 | Ed
 150 (2 rows)
 151
 152 -- sql, proretset = f, prorettype = record
 153 DROP VIEW vw_getfoo;
 154 DROP FUNCTION getfoo(int);
 155 CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 156 SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
 157  fooid | foosubid | fooname
 158 -------+----------+---------
 159      1 |        1 | Joe
 160 (1 row)
 161
 162 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
 163 (fooid int, foosubid int, fooname text);
 164 SELECT * FROM vw_getfoo;
 165  fooid | foosubid | fooname
 166 -------+----------+---------
 167      1 |        1 | Joe
 168 (1 row)
 169
 170 -- sql, proretset = t, prorettype = record
 171 DROP VIEW vw_getfoo;
 172 DROP FUNCTION getfoo(int);
 173 CREATE FUNCTION getfoo(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
 174 SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
 175  fooid | foosubid | fooname
 176 -------+----------+---------
 177      1 |        1 | Joe
 178      1 |        2 | Ed
 179 (2 rows)
 180
 181 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
 182 (fooid int, foosubid int, fooname text);
 183 SELECT * FROM vw_getfoo;
 184  fooid | foosubid | fooname
 185 -------+----------+---------
 186      1 |        1 | Joe
 187      1 |        2 | Ed
 188 (2 rows)
 189
 190 -- plpgsql, proretset = f, prorettype = b
 191 DROP VIEW vw_getfoo;
 192 DROP FUNCTION getfoo(int);
 193 CREATE FUNCTION getfoo(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid = $1; RETURN fooint; END;' LANGUAGE plpgsql;
 194 SELECT * FROM getfoo(1) AS t1;
 195  t1
 196 ----
 197   1
 198 (1 row)
 199
 200 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 201 SELECT * FROM vw_getfoo;
 202  getfoo
 203 --------
 204       1
 205 (1 row)
 206
 207 -- plpgsql, proretset = f, prorettype = c
 208 DROP VIEW vw_getfoo;
 209 DROP FUNCTION getfoo(int);
 210 CREATE FUNCTION getfoo(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE fooid = $1; RETURN footup; END;' LANGUAGE plpgsql;
 211 SELECT * FROM getfoo(1) AS t1;
 212  fooid | foosubid | fooname
 213 -------+----------+---------
 214      1 |        1 | Joe
 215 (1 row)
 216
 217 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
 218 SELECT * FROM vw_getfoo;
 219  fooid | foosubid | fooname
 220 -------+----------+---------
 221      1 |        1 | Joe
 222 (1 row)
 223
 224 DROP VIEW vw_getfoo;
 225 DROP FUNCTION getfoo(int);
 226 DROP FUNCTION foot(int);
 227 DROP TABLE foo2;
 228 DROP TABLE foo;
 229 -- Rescan tests --
 230 CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
 231 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foorescan_pkey" for table "foorescan"
 232 INSERT INTO foorescan values(5000,1,'abc.5000.1');
 233 INSERT INTO foorescan values(5001,1,'abc.5001.1');
 234 INSERT INTO foorescan values(5002,1,'abc.5002.1');
 235 INSERT INTO foorescan values(5003,1,'abc.5003.1');
 236 INSERT INTO foorescan values(5004,1,'abc.5004.1');
 237 INSERT INTO foorescan values(5005,1,'abc.5005.1');
 238 INSERT INTO foorescan values(5006,1,'abc.5006.1');
 239 INSERT INTO foorescan values(5007,1,'abc.5007.1');
 240 INSERT INTO foorescan values(5008,1,'abc.5008.1');
 241 INSERT INTO foorescan values(5009,1,'abc.5009.1');
 242 INSERT INTO foorescan values(5000,2,'abc.5000.2');
 243 INSERT INTO foorescan values(5001,2,'abc.5001.2');
 244 INSERT INTO foorescan values(5002,2,'abc.5002.2');
 245 INSERT INTO foorescan values(5003,2,'abc.5003.2');
 246 INSERT INTO foorescan values(5004,2,'abc.5004.2');
 247 INSERT INTO foorescan values(5005,2,'abc.5005.2');
 248 INSERT INTO foorescan values(5006,2,'abc.5006.2');
 249 INSERT INTO foorescan values(5007,2,'abc.5007.2');
 250 INSERT INTO foorescan values(5008,2,'abc.5008.2');
 251 INSERT INTO foorescan values(5009,2,'abc.5009.2');
 252 INSERT INTO foorescan values(5000,3,'abc.5000.3');
 253 INSERT INTO foorescan values(5001,3,'abc.5001.3');
 254 INSERT INTO foorescan values(5002,3,'abc.5002.3');
 255 INSERT INTO foorescan values(5003,3,'abc.5003.3');
 256 INSERT INTO foorescan values(5004,3,'abc.5004.3');
 257 INSERT INTO foorescan values(5005,3,'abc.5005.3');
 258 INSERT INTO foorescan values(5006,3,'abc.5006.3');
 259 INSERT INTO foorescan values(5007,3,'abc.5007.3');
 260 INSERT INTO foorescan values(5008,3,'abc.5008.3');
 261 INSERT INTO foorescan values(5009,3,'abc.5009.3');
 262 INSERT INTO foorescan values(5000,4,'abc.5000.4');
 263 INSERT INTO foorescan values(5001,4,'abc.5001.4');
 264 INSERT INTO foorescan values(5002,4,'abc.5002.4');
 265 INSERT INTO foorescan values(5003,4,'abc.5003.4');
 266 INSERT INTO foorescan values(5004,4,'abc.5004.4');
 267 INSERT INTO foorescan values(5005,4,'abc.5005.4');
 268 INSERT INTO foorescan values(5006,4,'abc.5006.4');
 269 INSERT INTO foorescan values(5007,4,'abc.5007.4');
 270 INSERT INTO foorescan values(5008,4,'abc.5008.4');
 271 INSERT INTO foorescan values(5009,4,'abc.5009.4');
 272 INSERT INTO foorescan values(5000,5,'abc.5000.5');
 273 INSERT INTO foorescan values(5001,5,'abc.5001.5');
 274 INSERT INTO foorescan values(5002,5,'abc.5002.5');
 275 INSERT INTO foorescan values(5003,5,'abc.5003.5');
 276 INSERT INTO foorescan values(5004,5,'abc.5004.5');
 277 INSERT INTO foorescan values(5005,5,'abc.5005.5');
 278 INSERT INTO foorescan values(5006,5,'abc.5006.5');
 279 INSERT INTO foorescan values(5007,5,'abc.5007.5');
 280 INSERT INTO foorescan values(5008,5,'abc.5008.5');
 281 INSERT INTO foorescan values(5009,5,'abc.5009.5');
 282 CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
 283 --invokes ExecFunctionReScan
 284 SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
 285  fooid | foosubid |  fooname
 286 -------+----------+------------
 287   5002 |        1 | abc.5002.1
 288   5002 |        2 | abc.5002.2
 289   5002 |        3 | abc.5002.3
 290   5002 |        4 | abc.5002.4
 291   5002 |        5 | abc.5002.5
 292   5003 |        1 | abc.5003.1
 293   5003 |        2 | abc.5003.2
 294   5003 |        3 | abc.5003.3
 295   5003 |        4 | abc.5003.4
 296   5003 |        5 | abc.5003.5
 297 (10 rows)
 298
 299 CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
 300 --invokes ExecFunctionReScan
 301 SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
 302  fooid | foosubid |  fooname
 303 -------+----------+------------
 304   5002 |        1 | abc.5002.1
 305   5002 |        2 | abc.5002.2
 306   5002 |        3 | abc.5002.3
 307   5002 |        4 | abc.5002.4
 308   5002 |        5 | abc.5002.5
 309   5003 |        1 | abc.5003.1
 310   5003 |        2 | abc.5003.2
 311   5003 |        3 | abc.5003.3
 312   5003 |        4 | abc.5003.4
 313   5003 |        5 | abc.5003.5
 314 (10 rows)
 315
 316 CREATE TABLE barrescan (fooid int primary key);
 317 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan"
 318 INSERT INTO barrescan values(5003);
 319 INSERT INTO barrescan values(5004);
 320 INSERT INTO barrescan values(5005);
 321 INSERT INTO barrescan values(5006);
 322 INSERT INTO barrescan values(5007);
 323 INSERT INTO barrescan values(5008);
 324 CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
 325 --invokes ExecFunctionReScan with chgParam != NULL
 326 SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
 327  fooid | foosubid |  fooname
 328 -------+----------+------------
 329   5003 |        1 | abc.5003.1
 330   5003 |        2 | abc.5003.2
 331   5003 |        3 | abc.5003.3
 332   5003 |        4 | abc.5003.4
 333   5003 |        5 | abc.5003.5
 334   5004 |        1 | abc.5004.1
 335   5004 |        2 | abc.5004.2
 336   5004 |        3 | abc.5004.3
 337   5004 |        4 | abc.5004.4
 338   5004 |        5 | abc.5004.5
 339   5005 |        1 | abc.5005.1
 340   5005 |        2 | abc.5005.2
 341   5005 |        3 | abc.5005.3
 342   5005 |        4 | abc.5005.4
 343   5005 |        5 | abc.5005.5
 344   5006 |        1 | abc.5006.1
 345   5006 |        2 | abc.5006.2
 346   5006 |        3 | abc.5006.3
 347   5006 |        4 | abc.5006.4
 348   5006 |        5 | abc.5006.5
 349   5007 |        1 | abc.5007.1
 350   5007 |        2 | abc.5007.2
 351   5007 |        3 | abc.5007.3
 352   5007 |        4 | abc.5007.4
 353   5007 |        5 | abc.5007.5
 354   5008 |        1 | abc.5008.1
 355   5008 |        2 | abc.5008.2
 356   5008 |        3 | abc.5008.3
 357   5008 |        4 | abc.5008.4
 358   5008 |        5 | abc.5008.5
 359 (30 rows)
 360
 361 SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
 362  fooid | max
 363 -------+-----
 364   5003 |   5
 365   5004 |   5
 366   5005 |   5
 367   5006 |   5
 368   5007 |   5
 369   5008 |   5
 370 (6 rows)
 371
 372 CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) ORDER BY 1,2;
 373 SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
 374  fooid | foosubid |  fooname
 375 -------+----------+------------
 376   5004 |        1 | abc.5004.1
 377   5004 |        2 | abc.5004.2
 378   5004 |        3 | abc.5004.3
 379   5004 |        4 | abc.5004.4
 380   5004 |        5 | abc.5004.5
 381 (5 rows)
 382
 383 CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid FROM foorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2;
 384 SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
 385  fooid | maxsubid
 386 -------+----------
 387   5003 |        5
 388   5004 |        5
 389   5005 |        5
 390   5006 |        5
 391   5007 |        5
 392   5008 |        5
 393 (6 rows)
 394
 395 DROP VIEW vw_foorescan;
 396 DROP VIEW fooview1;
 397 DROP VIEW fooview2;
 398 DROP FUNCTION foorescan(int,int);
 399 DROP FUNCTION foorescan(int);
 400 DROP TABLE foorescan;
 401 DROP TABLE barrescan;
 402 --
 403 -- Test cases involving OUT parameters
 404 --
 405 CREATE FUNCTION foo(in f1 int, out f2 int)
 406 AS 'select $1+1' LANGUAGE sql;
 407 SELECT foo(42);
 408  foo
 409 -----
 410   43
 411 (1 row)
 412
 413 SELECT * FROM foo(42);
 414  f2
 415 ----
 416  43
 417 (1 row)
 418
 419 SELECT * FROM foo(42) AS p(x);
 420  x
 421 ----
 422  43
 423 (1 row)
 424
 425 -- explicit spec of return type is OK
 426 CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS int
 427 AS 'select $1+1' LANGUAGE sql;
 428 -- error, wrong result type
 429 CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS float
 430 AS 'select $1+1' LANGUAGE sql;
 431 ERROR:  function result type must be integer because of OUT parameters
 432 -- with multiple OUT params you must get a RECORD result
 433 CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text) RETURNS int
 434 AS 'select $1+1' LANGUAGE sql;
 435 ERROR:  function result type must be record because of OUT parameters
 436 CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text)
 437 RETURNS record
 438 AS 'select $1+1' LANGUAGE sql;
 439 ERROR:  cannot change return type of existing function
 440 HINT:  Use DROP FUNCTION first.
 441 CREATE OR REPLACE FUNCTION foor(in f1 int, out f2 int, out text)
 442 AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
 443 SELECT f1, foor(f1) FROM int4_tbl;
 444      f1      |            foor
 445 -------------+----------------------------
 446            0 | (-1,0z)
 447       123456 | (123455,123456z)
 448      -123456 | (-123457,-123456z)
 449   2147483647 | (2147483646,2147483647z)
 450  -2147483647 | (-2147483648,-2147483647z)
 451 (5 rows)
 452
 453 SELECT * FROM foor(42);
 454  f2 | column2
 455 ----+---------
 456  41 | 42z
 457 (1 row)
 458
 459 SELECT * FROM foor(42) AS p(a,b);
 460  a  |  b
 461 ----+-----
 462  41 | 42z
 463 (1 row)
 464
 465 CREATE OR REPLACE FUNCTION foob(in f1 int, inout f2 int, out text)
 466 AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql;
 467 SELECT f1, foob(f1, f1/2) FROM int4_tbl;
 468      f1      |            foob
 469 -------------+----------------------------
 470            0 | (-1,0z)
 471       123456 | (61727,123456z)
 472      -123456 | (-61729,-123456z)
 473   2147483647 | (1073741822,2147483647z)
 474  -2147483647 | (-1073741824,-2147483647z)
 475 (5 rows)
 476
 477 SELECT * FROM foob(42, 99);
 478  f2 | column2
 479 ----+---------
 480  98 | 42z
 481 (1 row)
 482
 483 SELECT * FROM foob(42, 99) AS p(a,b);
 484  a  |  b
 485 ----+-----
 486  98 | 42z
 487 (1 row)
 488
 489 -- Can reference function with or without OUT params for DROP, etc
 490 DROP FUNCTION foo(int);
 491 DROP FUNCTION foor(in f2 int, out f1 int, out text);
 492 DROP FUNCTION foob(in f1 int, inout f2 int);
 493 --
 494 -- For my next trick, polymorphic OUT parameters
 495 --
 496 CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray)
 497 AS 'select $1, array[$1,$1]' LANGUAGE sql;
 498 SELECT dup(22);
 499       dup
 500 ----------------
 501  (22,"{22,22}")
 502 (1 row)
 503
 504 SELECT dup('xyz');      -- fails
 505 ERROR:  could not determine polymorphic type because input has type "unknown"
 506 SELECT dup('xyz'::text);
 507         dup
 508 -------------------
 509  (xyz,"{xyz,xyz}")
 510 (1 row)
 511
 512 SELECT * FROM dup('xyz'::text);
 513  f2  |    f3
 514 -----+-----------
 515  xyz | {xyz,xyz}
 516 (1 row)
 517
 518 -- equivalent specification
 519 CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
 520 AS 'select $1, array[$1,$1]' LANGUAGE sql;
 521 SELECT dup(22);
 522       dup
 523 ----------------
 524  (22,"{22,22}")
 525 (1 row)
 526
 527 DROP FUNCTION dup(anyelement);
 528 -- fails, no way to deduce outputs
 529 CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
 530 AS 'select $1, array[$1,$1]' LANGUAGE sql;
 531 ERROR:  cannot determine result data type
 532 DETAIL:  A function returning a polymorphic type must have at least one polymorphic argument.
 533 --
 534 -- table functions
 535 --
 536 CREATE OR REPLACE FUNCTION foo()
 537 RETURNS TABLE(a int)
 538 AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql;
 539 SELECT * FROM foo();
 540  a
 541 ---
 542  1
 543  2
 544  3
 545  4
 546  5
 547 (5 rows)
 548
 549 DROP FUNCTION foo();
 550 CREATE OR REPLACE FUNCTION foo(int)
 551 RETURNS TABLE(a int, b int)
 552 AS $$ SELECT a, b
 553          FROM generate_series(1,$1) a(a),
 554               generate_series(1,$1) b(b) $$ LANGUAGE sql;
 555 SELECT * FROM foo(3);
 556  a | b
 557 ---+---
 558  1 | 1
 559  1 | 2
 560  1 | 3
 561  2 | 1
 562  2 | 2
 563  2 | 3
 564  3 | 1
 565  3 | 2
 566  3 | 3
 567 (9 rows)
 568
 569 DROP FUNCTION foo(int);
 570 --
 571 -- some tests on SQL functions with RETURNING
 572 --
 573 create temp table tt(f1 serial, data text);
 574 NOTICE:  CREATE TABLE will create implicit sequence "tt_f1_seq" for serial column "tt.f1"
 575 create function insert_tt(text) returns int as
 576 $$ insert into tt(data) values($1) returning f1 $$
 577 language sql;
 578 select insert_tt('foo');
 579  insert_tt
 580 -----------
 581          1
 582 (1 row)
 583
 584 select insert_tt('bar');
 585  insert_tt
 586 -----------
 587          2
 588 (1 row)
 589
 590 select * from tt;
 591  f1 | data
 592 ----+------
 593   1 | foo
 594   2 | bar
 595 (2 rows)
 596
 597 -- insert will execute to completion even if function needs just 1 row
 598 create or replace function insert_tt(text) returns int as
 599 $$ insert into tt(data) values($1),($1||$1) returning f1 $$
 600 language sql;
 601 select insert_tt('fool');
 602  insert_tt
 603 -----------
 604          3
 605 (1 row)
 606
 607 select * from tt;
 608  f1 |   data
 609 ----+----------
 610   1 | foo
 611   2 | bar
 612   3 | fool
 613   4 | foolfool
 614 (4 rows)
 615
 616 -- setof does what's expected
 617 create or replace function insert_tt2(text,text) returns setof int as
 618 $$ insert into tt(data) values($1),($2) returning f1 $$
 619 language sql;
 620 select insert_tt2('foolish','barrish');
 621  insert_tt2
 622 ------------
 623           5
 624           6
 625 (2 rows)
 626
 627 select * from insert_tt2('baz','quux');
 628  insert_tt2
 629 ------------
 630           7
 631           8
 632 (2 rows)
 633
 634 select * from tt;
 635  f1 |   data
 636 ----+----------
 637   1 | foo
 638   2 | bar
 639   3 | fool
 640   4 | foolfool
 641   5 | foolish
 642   6 | barrish
 643   7 | baz
 644   8 | quux
 645 (8 rows)
 646
 647 -- limit doesn't prevent execution to completion
 648 select insert_tt2('foolish','barrish') limit 1;
 649  insert_tt2
 650 ------------
 651           9
 652 (1 row)
 653
 654 select * from tt;
 655  f1 |   data
 656 ----+----------
 657   1 | foo
 658   2 | bar
 659   3 | fool
 660   4 | foolfool
 661   5 | foolish
 662   6 | barrish
 663   7 | baz
 664   8 | quux
 665   9 | foolish
 666  10 | barrish
 667 (10 rows)
 668
 669 -- triggers will fire, too
 670 create function noticetrigger() returns trigger as $$
 671 begin
 672   raise notice 'noticetrigger % %', new.f1, new.data;
 673   return null;
 674 end $$ language plpgsql;
 675 create trigger tnoticetrigger after insert on tt for each row
 676 execute procedure noticetrigger();
 677 select insert_tt2('foolme','barme') limit 1;
 678 NOTICE:  noticetrigger 11 foolme
 679 CONTEXT:  SQL function "insert_tt2" statement 1
 680 NOTICE:  noticetrigger 12 barme
 681 CONTEXT:  SQL function "insert_tt2" statement 1
 682  insert_tt2
 683 ------------
 684          11
 685 (1 row)
 686
 687 select * from tt;
 688  f1 |   data
 689 ----+----------
 690   1 | foo
 691   2 | bar
 692   3 | fool
 693   4 | foolfool
 694   5 | foolish
 695   6 | barrish
 696   7 | baz
 697   8 | quux
 698   9 | foolish
 699  10 | barrish
 700  11 | foolme
 701  12 | barme
 702 (12 rows)
 703
 704 -- and rules work
 705 create temp table tt_log(f1 int, data text);
 706 create rule insert_tt_rule as on insert to tt do also
 707   insert into tt_log values(new.*);
 708 select insert_tt2('foollog','barlog') limit 1;
 709 NOTICE:  noticetrigger 13 foollog
 710 CONTEXT:  SQL function "insert_tt2" statement 1
 711 NOTICE:  noticetrigger 14 barlog
 712 CONTEXT:  SQL function "insert_tt2" statement 1
 713  insert_tt2
 714 ------------
 715          13
 716 (1 row)
 717
 718 select * from tt;
 719  f1 |   data
 720 ----+----------
 721   1 | foo
 722   2 | bar
 723   3 | fool
 724   4 | foolfool
 725   5 | foolish
 726   6 | barrish
 727   7 | baz
 728   8 | quux
 729   9 | foolish
 730  10 | barrish
 731  11 | foolme
 732  12 | barme
 733  13 | foollog
 734  14 | barlog
 735 (14 rows)
 736
 737 -- note that nextval() gets executed a second time in the rule expansion,
 738 -- which is expected.
 739 select * from tt_log;
 740  f1 |  data
 741 ----+---------
 742  15 | foollog
 743  16 | barlog
 744 (2 rows)
 745
 746 -- test case for a whole-row-variable bug
 747 create function foo1(n integer, out a text, out b text)
 748   returns setof record
 749   language sql
 750   as $$ select 'foo ' || i, 'bar ' || i from generate_series(1,$1) i $$;
 751 set work_mem='64kB';
 752 select t.a, t, t.a from foo1(10000) t limit 1;
 753    a   |         t         |   a
 754 -------+-------------------+-------
 755  foo 1 | ("foo 1","bar 1") | foo 1
 756 (1 row)
 757
 758 reset work_mem;
 759 select t.a, t, t.a from foo1(10000) t limit 1;
 760    a   |         t         |   a
 761 -------+-------------------+-------
 762  foo 1 | ("foo 1","bar 1") | foo 1
 763 (1 row)
 764
 765 drop function foo1(n integer);
 766 -- test use of SQL functions returning record
 767 -- this is supported in some cases where the query doesn't specify
 768 -- the actual record type ...
 769 create function array_to_set(anyarray) returns setof record as $$
 770   select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i
 771 $$ language sql strict immutable;
 772 select array_to_set(array['one', 'two']);
 773  array_to_set
 774 --------------
 775  (1,one)
 776  (2,two)
 777 (2 rows)
 778
 779 select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text);
 780  f1 | f2
 781 ----+-----
 782   1 | one
 783   2 | two
 784 (2 rows)
 785
 786 select * from array_to_set(array['one', 'two']); -- fail
 787 ERROR:  a column definition list is required for functions returning "record"
 788 LINE 1: select * from array_to_set(array['one', 'two']);
 789                       ^
 790 create temp table foo(f1 int8, f2 int8);
 791 create function testfoo() returns record as $$
 792   insert into foo values (1,2) returning *;
 793 $$ language sql;
 794 select testfoo();
 795  testfoo
 796 ---------
 797  (1,2)
 798 (1 row)
 799
 800 select * from testfoo() as t(f1 int8,f2 int8);
 801  f1 | f2
 802 ----+----
 803   1 |  2
 804 (1 row)
 805
 806 select * from testfoo(); -- fail
 807 ERROR:  a column definition list is required for functions returning "record"
 808 LINE 1: select * from testfoo();
 809                       ^
 810 drop function testfoo();
 811 create function testfoo() returns setof record as $$
 812   insert into foo values (1,2), (3,4) returning *;
 813 $$ language sql;
 814 select testfoo();
 815  testfoo
 816 ---------
 817  (1,2)
 818  (3,4)
 819 (2 rows)
 820
 821 select * from testfoo() as t(f1 int8,f2 int8);
 822  f1 | f2
 823 ----+----
 824   1 |  2
 825   3 |  4
 826 (2 rows)
 827
 828 select * from testfoo(); -- fail
 829 ERROR:  a column definition list is required for functions returning "record"
 830 LINE 1: select * from testfoo();
 831                       ^
 832 drop function testfoo();