Re: Pet Peeves?

On Fri, Jan 30, 2009 at 1:37 PM, Reg Me Please <regmeplease(at)gmail(dot)com> wrote:

> * LIMIT and OFFSET don't accept non-constant (or non function) values
>
no longer true with 8.4

gjaskie=# create table foo(a serial, b varchar);

gjaskie=# insert into table foo(b) select generate_series(1,10000,
(random()*100)::int);

gjaskie=# insert into foo(b) select generate_series(1,10000,
(random()*100)::int);

gjaskie=# insert into foo(b) select generate_series(1,10000,
(random()*100)::int);

gjaskie=# insert into foo(b) select generate_series(1,10000,
(random()*100)::int);

gjaskie=# select count(*) from foo;
count
-------
2019
(1 row)

gjaskie=# select * from foo order by random() limit (select a*random()
from foo order by random() limit 1);
a | b
------+------
1491 | 8941
892 | 5347
1609 | 9649
1796 | 9985
190 | 1135
1445 | 8665
194 | 1159
47 | 277
911 | 5461
1941 | 6481
196 | 1171
1995 | 8911
25 | 145
1430 | 8575
533 | 3193
172 | 1027
1111 | 6661
948 | 5683
148 | 883
99 | 589
173 | 1033
1224 | 7339
973 | 5833
1358 | 8143
1532 | 9187
165 | 985
1996 | 8956
82 | 487
820 | 4915
1185 | 7105
534 | 3199
57 | 337
2019 | 9991
(33 rows)

gjaskie=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.3.0 20080428 (Red Hat 4.3.0-8), 32-bit
(1 row)

--
GJ