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