Re: Dynamic limit for the number of records?

On Sat, 05 Oct 2002 16:13:37 +0530
Murali Mohan Kasetty <kasetty(at)india(dot)hp(dot)com> wrote:

> Is there a way to dynamically limit the number of records in a view
> based on
> the number of records in another table. Her e is an example:
>
> I have two views VIEW1 and VIEW2.
> The total number of records in VIEW1 and VIEW2 should be 20. So, if
> VIEW1
> has 10 records VIEW2 should have only 20 records, if VIEW1 has 5 records
>
> then VIEW2 should have only 25 records.
>
> I have tried LIMIT to limit the number of records. BUt, LIMIT requires
> that
> the number of recors be static. A

If using SEQUENCE instead of LIMIT, you can get the number of rows
in VIEW2. The following is an example of the way. As your circumstance
demands, fit it to your VIEW1 and VIEW2. But, under multi-sessions, I would
think you need to pay attention to the unexpected increment of SECUENCE.

Regards,
Masaru Sugawara

drop table tbl_a;
drop table tbl_b;
create table tbl_a (x int4, y int4);
create table tbl_b (z int4, w int4);
insert into tbl_a values(2, 22);
insert into tbl_a values(2, 22);
insert into tbl_a values(3, 31);
insert into tbl_a values(3, 31);
insert into tbl_a values(3, 33);
insert into tbl_a values(3, 33);
insert into tbl_a values(3, 33);
insert into tbl_b values(1,1);
insert into tbl_b values(1,2);
insert into tbl_b values(2,1);
insert into tbl_b values(2,2);
insert into tbl_b values(2,3);

create sequence seq_view_limit;

drop view view1;
create view view1 as
select * from tbl_b
where z = 2 -- some condition
;

drop view view2;
create view view2 as
select v2.x, v2.y
from (select v1.*, nextval('seq_view_limit') -1 as rank
from (select *, (select setval('seq_view_limit', 1))
from tbl_a
where x = 3 -- some condition
order by y -- if necessary
) as v1
limit all
) as v2,
(select count(*) as n from view1) as v3
where v2.rank + v3.n <= 5 -- total number of records
;

renew=# select * from view1;
z | w
---+---
2 | 1
2 | 2
2 | 3
(3 rows)

renew=# select * from view2;
x | y
---+----
3 | 31
3 | 31
(2 rows)

renew=# delete from tbl_b where w = 2;
DELETE 2

renew=# select * from view1;
z | w
---+---
2 | 1
2 | 3
(2 rows)

renew=# select * from view2;
x | y
---+----
3 | 31
3 | 31
3 | 33
(3 rows)