Re: Function to return list of all prime numbers in range

Hi Melvin,
Here is a slightly optimized version of this function....
It returns the exact same results, just runs about 1000x faster.
I've also marked it as "immutable", that's probably what you wanted,
not Volatile.

select all_prime(10000,11000)
-- Total runtime: 2868.264 ms

select all_prime2(10000,11000);
-- Total runtime: 3.662 ms

CREATE OR REPLACE FUNCTION public.all_prime2(v_start INT4, v_end INT4)
RETURNS TEXT AS $BODY$
DECLARE
v_test INT4;
v_divisor INT4;
v_prime_list TEXT;
BEGIN
<<OUTER>>
FOR v_test IN v_start .. v_end LOOP
IF v_test = 2 THEN
v_prime_list = '2';
END IF;

CONTINUE WHEN mod(v_test,2) = 0;

FOR v_divisor IN 3 .. ceil(sqrt(v_test)) BY 2 LOOP
CONTINUE OUTER WHEN mod(v_test,v_divisor) = 0;
END LOOP;

IF v_prime_list IS NOT NULL THEN
v_prime_list = v_prime_list || ',';
END IF;
v_prime_list = coalesce(v_prime_list,'') || v_test::text;
END LOOP OUTER;

RETURN v_prime_list;
END; $BODY$
LANGUAGE 'plpgsql' IMMUTABLE;

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Melvin Davidson
Sent: Monday, February 12, 2007 2:03 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Function to return list of all prime numbers in range

My apologies if this is the wrong mailing list.
I've created a function that returns a list of all prime numbers in a
range.
eg: SELECT public.all_prime(190, 223);
191,193,197,199,211,223
I'd like to submit this the the contrib lib, but I could not find the
correct
email list. Use as you wish.
The code is below.
========================================================================
==========
CREATE OR REPLACE FUNCTION public.all_prime(INT4, INT4)
RETURNS TEXT AS
-- Returns a list of all prime numbers in the range of $1 to $2
-- Contibuted by Melvin Davidson
-- Computer & Communication Technologies, Inc.
-- mdavidson(at)cctus(dot)com
$BODY$
DECLARE
v_start ALIAS FOR $1;
v_end ALIAS FOR $2;
v_test INT4;
v_divisor INT4;
v_prime_list TEXT DEFAULT '';
v_msg TEXT;
BEGIN
v_test = v_start;
WHILE (v_test <= v_end) LOOP
v_divisor = 2;
WHILE (v_divisor <= v_test) LOOP
IF mod(v_test, v_divisor) = 0 AND v_divisor <
v_test THEN
EXIT;
ELSE
IF mod(v_test, v_divisor) = 0 AND
v_divisor = v_test THEN
IF v_prime_list > '' THEN
v_prime_list =
v_prime_list || ',';
END IF;
v_prime_list = v_prime_list ||
v_test::text;
END IF;
END IF;
v_divisor = v_divisor +1;
END LOOP;
v_test = v_test + 1;
END LOOP;
RETURN v_prime_list;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
GRANT EXECUTE ON FUNCTION public.all_prime(INT4, INT4) TO public;
COMMENT ON FUNCTION public.all_prime(INT4, INT4) IS 'Returns list of all
prime numbers from $1 to $2';
========================================================================
==========