View for prime numbers

[Christian Kienert]

Hi,

We are just discussing "you can do almost everything
using a view". Of course the view might become a large
one, but it should be possible to do it with a view.

Any ideas on how to write a view that returns the
prime numbers within a table of numbers?

e.g.
create table NUMBERS (number integer);
create view PRIMES (number) as ???;

[VC]

Hello,

"Christian Kienert" <Ch***************@gmx.de> wrote in message
news:bv**********@ngspool-d02.news.aol.com...

Hi,

We are just discussing "you can do almost everything
using a view". Of course the view might become a large
one, but it should be possible to do it with a view.

Any ideas on how to write a view that returns the
prime numbers within a table of numbers?

e.g.
create table NUMBERS (number integer);
create view PRIMES (number) as ???;

Well, it's trivial if you define a function that checks for primality:

-- Naive primality check
create or replace function isprime(p_number int) return int as
l_isprime int default 1;
l_d int default 2;
begin
while l_d >= 2 and l_d <= sqrt(p_number) loop
if mod(p_number, l_d) = 0 then l_isprime := 0; end if;
l_d := l_d +1;
end loop;
if p_number = 1 then return 0; else return l_isprime; end if;
end;
/

create view primes as select * from numbers where isprime(number)=1;

Rgds.

VC