Re: Pet Peeves?

On Fri, Jan 30, 2009 at 1:37 PM, Reg Me Please <regmeplease@gmail.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