Re: SRF patch (was Re: [HACKERS] troubleshooting pointers) - Mailing list pgsql-patches

Tom Lane wrote:
 > It's not too soon to start thinking about documentation and
 > regression tests for SRFs ...

Attached is a regression test patch for SRFs. I based it on the test
scripts that I have been using, minus the C function tests and without
calls to random() -- figured random() wouldn't work too well for a
regression test ;-)

Joe

Index: src/test/regress/parallel_schedule
===================================================================
RCS file: /opt/src/cvs/pgsql/src/test/regress/parallel_schedule,v
retrieving revision 1.9
diff -c -r1.9 parallel_schedule
*** src/test/regress/parallel_schedule    5 Apr 2002 11:56:55 -0000    1.9
--- src/test/regress/parallel_schedule    19 May 2002 23:32:21 -0000
***************
*** 74,77 ****
  # The sixth group of parallel test
  # ----------
  # "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain
--- 74,77 ----
  # The sixth group of parallel test
  # ----------
  # "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain rangefuncs
Index: src/test/regress/serial_schedule
===================================================================
RCS file: /opt/src/cvs/pgsql/src/test/regress/serial_schedule,v
retrieving revision 1.9
diff -c -r1.9 serial_schedule
*** src/test/regress/serial_schedule    5 Apr 2002 11:56:55 -0000    1.9
--- src/test/regress/serial_schedule    19 May 2002 23:32:11 -0000
***************
*** 82,85 ****
  test: plpgsql
  test: temp
  test: domain
!
--- 82,85 ----
  test: plpgsql
  test: temp
  test: domain
! test: rangefuncs
Index: src/test/regress/expected/rangefuncs.out
===================================================================
RCS file: src/test/regress/expected/rangefuncs.out
diff -N src/test/regress/expected/rangefuncs.out
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- src/test/regress/expected/rangefuncs.out    20 May 2002 00:10:24 -0000
***************
*** 0 ****
--- 1,348 ----
+ 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;
+ NOTICE:  Adding missing FROM-clause entry for table "foo2"
+ ERROR:  FROM function expression may not 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;
+  getfoo
+ --------
+       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 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;
+  getfoo
+ --------
+       1
+       1
+ (2 rows)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  getfoo
+ --------
+       1
+       1
+ (2 rows)
+
+ -- sql, proretset = t, prorettype = b
+ 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;
+  getfoo
+ --------
+  Joe
+  Ed
+ (2 rows)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  getfoo
+ --------
+  Joe
+  Ed
+ (2 rows)
+
+ -- sql, proretset = f, prorettype = c
+ 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)
+
+ DROP VIEW vw_getfoo;
+ 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 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)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  fooid | foosubid | fooname
+ -------+----------+---------
+      1 |        1 | Joe
+      1 |        2 | Ed
+ (2 rows)
+
+ -- plpgsql, proretset = f, prorettype = b
+ 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;
+  getfoo
+ --------
+       1
+ (1 row)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  getfoo
+ --------
+       1
+ (1 row)
+
+ -- plpgsql, proretset = f, prorettype = c
+ 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)
+
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+  fooid | foosubid | fooname
+ -------+----------+---------
+      1 |        1 | Joe
+ (1 row)
+
+ DROP TABLE foo2;
+ DROP FUNCTION foot(int);
+ DROP TABLE foo;
+ DROP FUNCTION getfoo(int);
+ DROP VIEW vw_getfoo;
+ -- 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
FROMfoorescan(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
FROMfoorescan(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.fooidAND 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 TABLE foorescan;
+ DROP FUNCTION foorescan(int,int);
+ DROP VIEW vw_foorescan;
+ DROP TABLE barrescan;
+ DROP FUNCTION foorescan(int);
+ DROP VIEW fooview1;
+ DROP VIEW fooview2;
Index: src/test/regress/sql/rangefuncs.sql
===================================================================
RCS file: src/test/regress/sql/rangefuncs.sql
diff -N src/test/regress/sql/rangefuncs.sql
*** /dev/null    1 Jan 1970 00:00:00 -0000
--- src/test/regress/sql/rangefuncs.sql    19 May 2002 23:52:46 -0000
***************
*** 0 ****
--- 1,181 ----
+ 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;
+
+ -- 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;
+
+ -- function in subselect
+ select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
+
+ -- function in subselect
+ select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
+
+ -- nested functions
+ select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
+
+ CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
+ 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;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- sql, proretset = t, prorettype = b
+ 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;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- sql, proretset = t, prorettype = b
+ 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;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- sql, proretset = f, prorettype = c
+ 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;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- sql, proretset = t, prorettype = c
+ 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;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- plpgsql, proretset = f, prorettype = b
+ 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;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ -- plpgsql, proretset = f, prorettype = c
+ 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;
+ DROP VIEW vw_getfoo;
+ CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
+ SELECT * FROM vw_getfoo;
+
+ DROP TABLE foo2;
+ DROP FUNCTION foot(int);
+ DROP TABLE foo;
+ DROP FUNCTION getfoo(int);
+ DROP VIEW vw_getfoo;
+
+ -- Rescan tests --
+ CREATE TABLE foorescan (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
+ 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;
+
+ 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;
+
+ CREATE TABLE barrescan (fooid int primary key);
+ 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; 
+ SELECT b.fooid, max(f.foosubid) FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid
FROMfoorescan(b.fooid)) GROUP BY b.fooid ORDER BY 1,2; 
+
+ CREATE VIEW fooview1 AS SELECT f.* FROM barrescan b, foorescan f WHERE f.fooid = b.fooid AND b.fooid IN (SELECT fooid
FROMfoorescan(b.fooid)) ORDER BY 1,2; 
+ SELECT * FROM fooview1 AS fv WHERE fv.fooid = 5004;
+
+ CREATE VIEW fooview2 AS SELECT b.fooid, max(f.foosubid) AS maxsubid FROM barrescan b, foorescan f WHERE f.fooid =
b.fooidAND 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;
+
+ DROP TABLE foorescan;
+ DROP FUNCTION foorescan(int,int);
+ DROP VIEW vw_foorescan;
+ DROP TABLE barrescan;
+ DROP FUNCTION foorescan(int);
+ DROP VIEW fooview1;
+ DROP VIEW fooview2;