path: root/src/test/regress/expected/rangefuncs.out

SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%';
       name        | setting 
-------------------+---------
 enable_bitmapscan | on
 enable_hashagg    | on
 enable_hashjoin   | on
 enable_indexscan  | on
 enable_mergejoin  | on
 enable_nestloop   | on
 enable_seqscan    | on
 enable_sort       | on
 enable_tidscan    | on
(9 rows)

CREATE TABLE foo2(fooid int, f2 int);
INSERT INTO foo2 VALUES(1, 11);
INSERT INTO foo2 VALUES(2, 22);
INSERT INTO foo2 VALUES(1, 111);
CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-- supposed to fail with ERROR
select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
ERROR:  function expression in FROM cannot refer to other relations of same query level
-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
 fooid | f2  
-------+-----
     1 |  11
     1 | 111
     2 |  22
(3 rows)

-- function in subselect
select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
 fooid | f2  
-------+-----
     1 |  11
     1 | 111
(2 rows)

-- function in subselect
select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
 fooid | f2  
-------+-----
     1 |  11
     1 | 111
(2 rows)

-- nested functions
select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
 fooid | f2  
-------+-----
     1 |  11
     1 | 111
(2 rows)

CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
INSERT INTO foo VALUES(1,1,'Joe');
INSERT INTO foo VALUES(1,2,'Ed');
INSERT INTO foo VALUES(2,1,'Mary');
-- sql, proretset = f, prorettype = b
CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
 t1 
----
  1
(1 row)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
 getfoo 
--------
      1
(1 row)

-- sql, proretset = t, prorettype = b
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
 t1 
----
  1
  1
(2 rows)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
 getfoo 
--------
      1
      1
(2 rows)

-- sql, proretset = t, prorettype = b
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
 t1  
-----
 Joe
 Ed
(2 rows)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
 getfoo 
--------
 Joe
 Ed
(2 rows)

-- sql, proretset = f, prorettype = c
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
(1 row)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
(1 row)

-- sql, proretset = t, prorettype = c
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

-- sql, proretset = f, prorettype = record
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
(1 row)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS 
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
(1 row)

-- sql, proretset = t, prorettype = record
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
CREATE FUNCTION getfoo(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1(fooid int, foosubid int, fooname text);
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1) AS
(fooid int, foosubid int, fooname text);
SELECT * FROM vw_getfoo;
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

-- plpgsql, proretset = f, prorettype = b
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
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;
SELECT * FROM getfoo(1) AS t1;
 t1 
----
  1
(1 row)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
 getfoo 
--------
      1
(1 row)

-- plpgsql, proretset = f, prorettype = c
DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
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;
SELECT * FROM getfoo(1) AS t1;
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
(1 row)

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
 fooid | foosubid | fooname 
-------+----------+---------
     1 |        1 | Joe
(1 row)

DROP VIEW vw_getfoo;
DROP FUNCTION getfoo(int);
DROP FUNCTION foot(int);
DROP TABLE foo2;
DROP TABLE foo;
-- Rescan tests --
CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foorescan_pkey" for table "foorescan"
INSERT INTO foorescan values(5000,1,'abc.5000.1');
INSERT INTO foorescan values(5001,1,'abc.5001.1');
INSERT INTO foorescan values(5002,1,'abc.5002.1');
INSERT INTO foorescan values(5003,1,'abc.5003.1');
INSERT INTO foorescan values(5004,1,'abc.5004.1');
INSERT INTO foorescan values(5005,1,'abc.5005.1');
INSERT INTO foorescan values(5006,1,'abc.5006.1');
INSERT INTO foorescan values(5007,1,'abc.5007.1');
INSERT INTO foorescan values(5008,1,'abc.5008.1');
INSERT INTO foorescan values(5009,1,'abc.5009.1');
INSERT INTO foorescan values(5000,2,'abc.5000.2');
INSERT INTO foorescan values(5001,2,'abc.5001.2');
INSERT INTO foorescan values(5002,2,'abc.5002.2');
INSERT INTO foorescan values(5003,2,'abc.5003.2');
INSERT INTO foorescan values(5004,2,'abc.5004.2');
INSERT INTO foorescan values(5005,2,'abc.5005.2');
INSERT INTO foorescan values(5006,2,'abc.5006.2');
INSERT INTO foorescan values(5007,2,'abc.5007.2');
INSERT INTO foorescan values(5008,2,'abc.5008.2');
INSERT INTO foorescan values(5009,2,'abc.5009.2');
INSERT INTO foorescan values(5000,3,'abc.5000.3');
INSERT INTO foorescan values(5001,3,'abc.5001.3');
INSERT INTO foorescan values(5002,3,'abc.5002.3');
INSERT INTO foorescan values(5003,3,'abc.5003.3');
INSERT INTO foorescan values(5004,3,'abc.5004.3');
INSERT INTO foorescan values(5005,3,'abc.5005.3');
INSERT INTO foorescan values(5006,3,'abc.5006.3');
INSERT INTO foorescan values(5007,3,'abc.5007.3');
INSERT INTO foorescan values(5008,3,'abc.5008.3');
INSERT INTO foorescan values(5009,3,'abc.5009.3');
INSERT INTO foorescan values(5000,4,'abc.5000.4');
INSERT INTO foorescan values(5001,4,'abc.5001.4');
INSERT INTO foorescan values(5002,4,'abc.5002.4');
INSERT INTO foorescan values(5003,4,'abc.5003.4');
INSERT INTO foorescan values(5004,4,'abc.5004.4');
INSERT INTO foorescan values(5005,4,'abc.5005.4');
INSERT INTO foorescan values(5006,4,'abc.5006.4');
INSERT INTO foorescan values(5007,4,'abc.5007.4');
INSERT INTO foorescan values(5008,4,'abc.5008.4');
INSERT INTO foorescan values(5009,4,'abc.5009.4');
INSERT INTO foorescan values(5000,5,'abc.5000.5');
INSERT INTO foorescan values(5001,5,'abc.5001.5');
INSERT INTO foorescan values(5002,5,'abc.5002.5');
INSERT INTO foorescan values(5003,5,'abc.5003.5');
INSERT INTO foorescan values(5004,5,'abc.5004.5');
INSERT INTO foorescan values(5005,5,'abc.5005.5');
INSERT INTO foorescan values(5006,5,'abc.5006.5');
INSERT INTO foorescan values(5007,5,'abc.5007.5');
INSERT INTO foorescan values(5008,5,'abc.5008.5');
INSERT INTO foorescan values(5009,5,'abc.5009.5');
CREATE FUNCTION foorescan(int,int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid >= $1 and fooid < $2 ;' LANGUAGE SQL;
--invokes ExecFunctionReScan
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM foorescan(5002,5004)) ORDER BY 1,2;
 fooid | foosubid |  fooname   
-------+----------+------------
  5002 |        1 | abc.5002.1
  5002 |        2 | abc.5002.2
  5002 |        3 | abc.5002.3
  5002 |        4 | abc.5002.4
  5002 |        5 | abc.5002.5
  5003 |        1 | abc.5003.1
  5003 |        2 | abc.5003.2
  5003 |        3 | abc.5003.3
  5003 |        4 | abc.5003.4
  5003 |        5 | abc.5003.5
(10 rows)

CREATE VIEW vw_foorescan AS SELECT * FROM foorescan(5002,5004);
--invokes ExecFunctionReScan
SELECT * FROM foorescan f WHERE f.fooid IN (SELECT fooid FROM vw_foorescan) ORDER BY 1,2;
 fooid | foosubid |  fooname   
-------+----------+------------
  5002 |        1 | abc.5002.1
  5002 |        2 | abc.5002.2
  5002 |        3 | abc.5002.3
  5002 |        4 | abc.5002.4
  5002 |        5 | abc.5002.5
  5003 |        1 | abc.5003.1
  5003 |        2 | abc.5003.2
  5003 |        3 | abc.5003.3
  5003 |        4 | abc.5003.4
  5003 |        5 | abc.5003.5
(10 rows)

CREATE TABLE barrescan (fooid int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "barrescan_pkey" for table "barrescan"
INSERT INTO barrescan values(5003);
INSERT INTO barrescan values(5004);
INSERT INTO barrescan values(5005);
INSERT INTO barrescan values(5006);
INSERT INTO barrescan values(5007);
INSERT INTO barrescan values(5008);
CREATE FUNCTION foorescan(int) RETURNS setof foorescan AS 'SELECT * FROM foorescan WHERE fooid = $1;' LANGUAGE SQL;
--invokes ExecFunctionReScan with chgParam != NULL
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;
 fooid | foosubid |  fooname   
-------+----------+------------
  5003 |        1 | abc.5003.1
  5003 |        2 | abc.5003.2
  5003 |        3 | abc.5003.3
  5003 |        4 | abc.5003.4
  5003 |        5 | abc.5003.5
  5004 |        1 | abc.5004.1
  5004 |        2 | abc.5004.2
  5004 |        3 | abc.5004.3
  5004 |        4 | abc.5004.4
  5004 |        5 | abc.5004.5
  5005 |        1 | abc.5005.1
  5005 |        2 | abc.5005.2
  5005 |        3 | abc.5005.3
  5005 |        4 | abc.5005.4
  5005 |        5 | abc.5005.5
  5006 |        1 | abc.5006.1
  5006 |        2 | abc.5006.2
  5006 |        3 | abc.5006.3
  5006 |        4 | abc.5006.4
  5006 |        5 | abc.5006.5
  5007 |        1 | abc.5007.1
  5007 |        2 | abc.5007.2
  5007 |        3 | abc.5007.3
  5007 |        4 | abc.5007.4
  5007 |        5 | abc.5007.5
  5008 |        1 | abc.5008.1
  5008 |        2 | abc.5008.2
  5008 |        3 | abc.5008.3
  5008 |        4 | abc.5008.4
  5008 |        5 | abc.5008.5
(30 rows)

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;
 fooid | max 
-------+-----
  5003 |   5
  5004 |   5
  5005 |   5
  5006 |   5
  5007 |   5
  5008 |   5
(6 rows)

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;
SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
 fooid | foosubid |  fooname   
-------+----------+------------
  5004 |        1 | abc.5004.1
  5004 |        2 | abc.5004.2
  5004 |        3 | abc.5004.3
  5004 |        4 | abc.5004.4
  5004 |        5 | abc.5004.5
(5 rows)

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;
SELECT * FROM fooview2 AS fv WHERE fv.maxsubid = 5;
 fooid | maxsubid 
-------+----------
  5003 |        5
  5004 |        5
  5005 |        5
  5006 |        5
  5007 |        5
  5008 |        5
(6 rows)

DROP VIEW vw_foorescan;
DROP VIEW fooview1;
DROP VIEW fooview2;
DROP FUNCTION foorescan(int,int);
DROP FUNCTION foorescan(int);
DROP TABLE foorescan;
DROP TABLE barrescan;
--
-- Test cases involving OUT parameters
--
CREATE FUNCTION foo(in f1 int, out f2 int)
AS 'select $1+1' LANGUAGE sql;
SELECT foo(42);
 foo 
-----
  43
(1 row)

SELECT * FROM foo(42);
 f2 
----
 43
(1 row)

SELECT * FROM foo(42) AS p(x);
 x  
----
 43
(1 row)

-- explicit spec of return type is OK
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS int
AS 'select $1+1' LANGUAGE sql;
-- error, wrong result type
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS float
AS 'select $1+1' LANGUAGE sql;
ERROR:  function result type must be integer because of OUT parameters
-- with multiple OUT params you must get a RECORD result
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text) RETURNS int
AS 'select $1+1' LANGUAGE sql;
ERROR:  function result type must be record because of OUT parameters
CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text)
RETURNS record
AS 'select $1+1' LANGUAGE sql;
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION first.
CREATE OR REPLACE FUNCTION foor(in f1 int, out f2 int, out text)
AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
SELECT f1, foor(f1) FROM int4_tbl;
     f1      |            foor            
-------------+----------------------------
           0 | (-1,0z)
      123456 | (123455,123456z)
     -123456 | (-123457,-123456z)
  2147483647 | (2147483646,2147483647z)
 -2147483647 | (-2147483648,-2147483647z)
(5 rows)

SELECT * FROM foor(42);
 f2 | column2 
----+---------
 41 | 42z
(1 row)

SELECT * FROM foor(42) AS p(a,b);
 a  |  b  
----+-----
 41 | 42z
(1 row)

CREATE OR REPLACE FUNCTION foob(in f1 int, inout f2 int, out text)
AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql;
SELECT f1, foob(f1, f1/2) FROM int4_tbl;
     f1      |            foob            
-------------+----------------------------
           0 | (-1,0z)
      123456 | (61727,123456z)
     -123456 | (-61729,-123456z)
  2147483647 | (1073741822,2147483647z)
 -2147483647 | (-1073741824,-2147483647z)
(5 rows)

SELECT * FROM foob(42, 99);
 f2 | column2 
----+---------
 98 | 42z
(1 row)

SELECT * FROM foob(42, 99) AS p(a,b);
 a  |  b  
----+-----
 98 | 42z
(1 row)

-- Can reference function with or without OUT params for DROP, etc
DROP FUNCTION foo(int);
DROP FUNCTION foor(in f2 int, out f1 int, out text);
DROP FUNCTION foob(in f1 int, inout f2 int);
--
-- For my next trick, polymorphic OUT parameters
--
CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
SELECT dup(22);
      dup       
----------------
 (22,"{22,22}")
(1 row)

SELECT dup('xyz');	-- fails
ERROR:  could not determine polymorphic type because input has type "unknown"
SELECT dup('xyz'::text);
        dup        
-------------------
 (xyz,"{xyz,xyz}")
(1 row)

SELECT * FROM dup('xyz'::text);
 f2  |    f3     
-----+-----------
 xyz | {xyz,xyz}
(1 row)

-- equivalent specification
CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
SELECT dup(22);
      dup       
----------------
 (22,"{22,22}")
(1 row)

DROP FUNCTION dup(anyelement);
-- fails, no way to deduce outputs
CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
ERROR:  cannot determine result data type
DETAIL:  A function returning a polymorphic type must have at least one polymorphic argument.
-- test case for a whole-row-variable bug
create function foo1(n integer, out a text, out b text)
  returns setof record
  language sql
  as $$ select 'foo ' || i, 'bar ' || i from generate_series(1,$1) i $$;
set work_mem='64kB';
select t.a, t, t.a from foo1(10000) t limit 1;
   a   |         t         |   a   
-------+-------------------+-------
 foo 1 | ("foo 1","bar 1") | foo 1
(1 row)

reset work_mem;
select t.a, t, t.a from foo1(10000) t limit 1;
   a   |         t         |   a   
-------+-------------------+-------
 foo 1 | ("foo 1","bar 1") | foo 1
(1 row)

drop function foo1(n integer);
-- check handling of a SQL function with multiple OUT params (bug #5777)
create or replace function foobar(out integer, out numeric) as
$$ select (1, 2.1) $$ language sql;
select * from foobar();
 column1 | column2 
---------+---------
       1 |     2.1
(1 row)

create or replace function foobar(out integer, out numeric) as
$$ select (1, 2) $$ language sql;
select * from foobar();  -- fail
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned type integer at ordinal position 2, but query expects numeric.
create or replace function foobar(out integer, out numeric) as
$$ select (1, 2.1, 3) $$ language sql;
select * from foobar();  -- fail
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned row contains 3 attributes, but query expects 2.
drop function foobar();