Added LIMIT/OFFSET functionality including new regression test for it.

Removed CURRENT keyword for rule queries and changed rules regression
accordingly. CURRENT has beed announced to disappear in v6.5.

Jan

5 files changed, 164 insertions, 26 deletions

diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out
new file mode 100644
index 00000000000..a46515d3343
--- /dev/null
+++ b/src/test/regress/expected/limit.out
@@ -0,0 +1,106 @@
+QUERY: SELECT ''::text AS two, unique1, unique2, stringu1
+		FROM onek WHERE unique1 > 50
+		ORDER BY unique1 LIMIT 2;
+two|unique1|unique2|stringu1
+---+-------+-------+--------
+   |     51|     76|ZBAAAA  
+   |     52|    985|ACAAAA  
+(2 rows)
+
+QUERY: SELECT ''::text AS five, unique1, unique2, stringu1
+		FROM onek WHERE unique1 > 60
+		ORDER BY unique1 LIMIT 5;
+five|unique1|unique2|stringu1
+----+-------+-------+--------
+    |     61|    560|JCAAAA  
+    |     62|    633|KCAAAA  
+    |     63|    296|LCAAAA  
+    |     64|    479|MCAAAA  
+    |     65|     64|NCAAAA  
+(5 rows)
+
+QUERY: SELECT ''::text AS two, unique1, unique2, stringu1
+		FROM onek WHERE unique1 > 60 AND unique1 < 63
+		ORDER BY unique1 LIMIT 5;
+two|unique1|unique2|stringu1
+---+-------+-------+--------
+   |     61|    560|JCAAAA  
+   |     62|    633|KCAAAA  
+(2 rows)
+
+QUERY: SELECT ''::text AS three, unique1, unique2, stringu1
+		FROM onek WHERE unique1 > 100
+		ORDER BY unique1 LIMIT 3 OFFSET 20;
+three|unique1|unique2|stringu1
+-----+-------+-------+--------
+     |    121|    700|REAAAA  
+     |    122|    519|SEAAAA  
+     |    123|    777|TEAAAA  
+(3 rows)
+
+QUERY: SELECT ''::text AS zero, unique1, unique2, stringu1
+		FROM onek WHERE unique1 < 50
+		ORDER BY unique1 DESC LIMIT 8 OFFSET 99;
+zero|unique1|unique2|stringu1
+----+-------+-------+--------
+(0 rows)
+
+QUERY: SELECT ''::text AS eleven, unique1, unique2, stringu1
+		FROM onek WHERE unique1 < 50
+		ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
+eleven|unique1|unique2|stringu1
+------+-------+-------+--------
+      |     10|    520|KAAAAA  
+      |      9|     49|JAAAAA  
+      |      8|    653|IAAAAA  
+      |      7|    647|HAAAAA  
+      |      6|    978|GAAAAA  
+      |      5|    541|FAAAAA  
+      |      4|    833|EAAAAA  
+      |      3|    431|DAAAAA  
+      |      2|    326|CAAAAA  
+      |      1|    214|BAAAAA  
+      |      0|    998|AAAAAA  
+(11 rows)
+
+QUERY: SELECT ''::text AS ten, unique1, unique2, stringu1
+		FROM onek
+		ORDER BY unique1 OFFSET 990;
+ten|unique1|unique2|stringu1
+---+-------+-------+--------
+   |    990|    369|CMAAAA  
+   |    991|    426|DMAAAA  
+   |    992|    363|EMAAAA  
+   |    993|    661|FMAAAA  
+   |    994|    695|GMAAAA  
+   |    995|    144|HMAAAA  
+   |    996|    258|IMAAAA  
+   |    997|     21|JMAAAA  
+   |    998|    549|KMAAAA  
+   |    999|    152|LMAAAA  
+(10 rows)
+
+QUERY: SELECT ''::text AS five, unique1, unique2, stringu1
+		FROM onek
+		ORDER BY unique1 OFFSET 990 LIMIT 5;
+five|unique1|unique2|stringu1
+----+-------+-------+--------
+    |    990|    369|CMAAAA  
+    |    991|    426|DMAAAA  
+    |    992|    363|EMAAAA  
+    |    993|    661|FMAAAA  
+    |    994|    695|GMAAAA  
+(5 rows)
+
+QUERY: SELECT ''::text AS five, unique1, unique2, stringu1
+		FROM onek
+		ORDER BY unique1 LIMIT 5, 900;
+five|unique1|unique2|stringu1
+----+-------+-------+--------
+    |    900|    913|QIAAAA  
+    |    901|    931|RIAAAA  
+    |    902|    702|SIAAAA  
+    |    903|    641|TIAAAA  
+    |    904|    793|UIAAAA  
+(5 rows)
+
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 68dc16c8690..7fb9fd25aa6 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -6,39 +6,39 @@ QUERY: create rule rtest_v1_ins as on insert to rtest_v1 do instead
 	insert into rtest_t1 values (new.a, new.b);
 QUERY: create rule rtest_v1_upd as on update to rtest_v1 do instead
 	update rtest_t1 set a = new.a, b = new.b
-	where a = current.a;
+	where a = old.a;
 QUERY: create rule rtest_v1_del as on delete to rtest_v1 do instead
-	delete from rtest_t1 where a = current.a;
+	delete from rtest_t1 where a = old.a;
 QUERY: create table rtest_system (sysname text, sysdesc text);
 QUERY: create table rtest_interface (sysname text, ifname text);
 QUERY: create table rtest_person (pname text, pdesc text);
 QUERY: create table rtest_admin (pname text, sysname text);
 QUERY: create rule rtest_sys_upd as on update to rtest_system do (
 	update rtest_interface set sysname = new.sysname
-		where sysname = current.sysname;
+		where sysname = old.sysname;
 	update rtest_admin set sysname = new.sysname
-		where sysname = current.sysname
+		where sysname = old.sysname
 	);
 QUERY: create rule rtest_sys_del as on delete to rtest_system do (
-	delete from rtest_interface where sysname = current.sysname;
-	delete from rtest_admin where sysname = current.sysname;
+	delete from rtest_interface where sysname = old.sysname;
+	delete from rtest_admin where sysname = old.sysname;
 	);
 QUERY: create rule rtest_pers_upd as on update to rtest_person do
-	update rtest_admin set pname = new.pname where pname = current.pname;
+	update rtest_admin set pname = new.pname where pname = old.pname;
 QUERY: create rule rtest_pers_del as on delete to rtest_person do
-	delete from rtest_admin where pname = current.pname;
+	delete from rtest_admin where pname = old.pname;
 QUERY: create table rtest_emp (ename char(20), salary money);
 QUERY: create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money);
 QUERY: create table rtest_empmass (ename char(20), salary money);
 QUERY: create rule rtest_emp_ins as on insert to rtest_emp do
 	insert into rtest_emplog values (new.ename, current_user,
 			'hired', new.salary, '0.00');
-QUERY: create rule rtest_emp_upd as on update to rtest_emp where new.salary != current.salary do
+QUERY: create rule rtest_emp_upd as on update to rtest_emp where new.salary != old.salary do
 	insert into rtest_emplog values (new.ename, current_user,
-			'honored', new.salary, current.salary);
+			'honored', new.salary, old.salary);
 QUERY: create rule rtest_emp_del as on delete to rtest_emp do
-	insert into rtest_emplog values (current.ename, current_user,
-			'fired', '0.00', current.salary);
+	insert into rtest_emplog values (old.ename, current_user,
+			'fired', '0.00', old.salary);
 QUERY: create table rtest_t4 (a int4, b text);
 QUERY: create table rtest_t5 (a int4, b text);
 QUERY: create table rtest_t6 (a int4, b text);
@@ -131,7 +131,7 @@ a| b
 QUERY: delete from rtest_v1 where b = 12;
 QUERY: select * from rtest_v1;
 ** Remember the delete rule on rtest_v1: It says
-** DO INSTEAD DELETE FROM rtest_t1 WHERE a = current.a
+** DO INSTEAD DELETE FROM rtest_t1 WHERE a = old.a
 ** So this time both rows with a = 2 must get deleted
 a| b
 -+--
diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql
new file mode 100644
index 00000000000..eb3bf0e7933
--- /dev/null
+++ b/src/test/regress/sql/limit.sql
@@ -0,0 +1,31 @@
+--
+-- Check the LIMIT/OFFSET feature of SELECT
+--
+
+SELECT ''::text AS two, unique1, unique2, stringu1 
+		FROM onek WHERE unique1 > 50 
+		ORDER BY unique1 LIMIT 2;
+SELECT ''::text AS five, unique1, unique2, stringu1 
+		FROM onek WHERE unique1 > 60 
+		ORDER BY unique1 LIMIT 5;
+SELECT ''::text AS two, unique1, unique2, stringu1 
+		FROM onek WHERE unique1 > 60 AND unique1 < 63
+		ORDER BY unique1 LIMIT 5;
+SELECT ''::text AS three, unique1, unique2, stringu1 
+		FROM onek WHERE unique1 > 100 
+		ORDER BY unique1 LIMIT 3 OFFSET 20;
+SELECT ''::text AS zero, unique1, unique2, stringu1 
+		FROM onek WHERE unique1 < 50 
+		ORDER BY unique1 DESC LIMIT 8 OFFSET 99;
+SELECT ''::text AS eleven, unique1, unique2, stringu1 
+		FROM onek WHERE unique1 < 50 
+		ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
+SELECT ''::text AS ten, unique1, unique2, stringu1 
+		FROM onek
+		ORDER BY unique1 OFFSET 990;
+SELECT ''::text AS five, unique1, unique2, stringu1 
+		FROM onek
+		ORDER BY unique1 OFFSET 990 LIMIT 5;
+SELECT ''::text AS five, unique1, unique2, stringu1 
+		FROM onek
+		ORDER BY unique1 LIMIT 5, 900;
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index e148489d9a8..319a2066522 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -16,9 +16,9 @@ create rule rtest_v1_ins as on insert to rtest_v1 do instead
 	insert into rtest_t1 values (new.a, new.b);
 create rule rtest_v1_upd as on update to rtest_v1 do instead
 	update rtest_t1 set a = new.a, b = new.b
-	where a = current.a;
+	where a = old.a;
 create rule rtest_v1_del as on delete to rtest_v1 do instead
-	delete from rtest_t1 where a = current.a;
+	delete from rtest_t1 where a = old.a;
 
 --
 -- Tables and rules for the constraint update/delete test
@@ -35,21 +35,21 @@ create table rtest_admin (pname text, sysname text);
 
 create rule rtest_sys_upd as on update to rtest_system do (
 	update rtest_interface set sysname = new.sysname 
-		where sysname = current.sysname;
+		where sysname = old.sysname;
 	update rtest_admin set sysname = new.sysname 
-		where sysname = current.sysname
+		where sysname = old.sysname
 	);
 
 create rule rtest_sys_del as on delete to rtest_system do (
-	delete from rtest_interface where sysname = current.sysname;
-	delete from rtest_admin where sysname = current.sysname;
+	delete from rtest_interface where sysname = old.sysname;
+	delete from rtest_admin where sysname = old.sysname;
 	);
 
 create rule rtest_pers_upd as on update to rtest_person do 
-	update rtest_admin set pname = new.pname where pname = current.pname;
+	update rtest_admin set pname = new.pname where pname = old.pname;
 
 create rule rtest_pers_del as on delete to rtest_person do 
-	delete from rtest_admin where pname = current.pname;
+	delete from rtest_admin where pname = old.pname;
 
 --
 -- Tables and rules for the logging test
@@ -62,13 +62,13 @@ create rule rtest_emp_ins as on insert to rtest_emp do
 	insert into rtest_emplog values (new.ename, current_user,
 			'hired', new.salary, '0.00');
 
-create rule rtest_emp_upd as on update to rtest_emp where new.salary != current.salary do
+create rule rtest_emp_upd as on update to rtest_emp where new.salary != old.salary do
 	insert into rtest_emplog values (new.ename, current_user,
-			'honored', new.salary, current.salary);
+			'honored', new.salary, old.salary);
 
 create rule rtest_emp_del as on delete to rtest_emp do
-	insert into rtest_emplog values (current.ename, current_user,
-			'fired', '0.00', current.salary);
+	insert into rtest_emplog values (old.ename, current_user,
+			'fired', '0.00', old.salary);
 
 --
 -- Tables and rules for the multiple cascaded qualified instead
@@ -177,7 +177,7 @@ insert into rtest_v1 values (2, 12);
 insert into rtest_v1 values (2, 13);
 select * from rtest_v1;
 ** Remember the delete rule on rtest_v1: It says
-** DO INSTEAD DELETE FROM rtest_t1 WHERE a = current.a
+** DO INSTEAD DELETE FROM rtest_t1 WHERE a = old.a
 ** So this time both rows with a = 2 must get deleted
 \p
 \r
diff --git a/src/test/regress/sql/tests b/src/test/regress/sql/tests
index ddc2365d2b4..44529af2b27 100644
--- a/src/test/regress/sql/tests
+++ b/src/test/regress/sql/tests
@@ -62,6 +62,7 @@ select_views
 alter_table
 portals_p2
 rules
+limit
 install_plpgsql
 plpgsql
 temp