Sunday, December 25, 2011

Postgresql -plpgsql. Generating primes as SETOF RECORD

In my previous attempt I returned the result by just printing the prime numbers as messages, but obviously you couldn't use them for further processing. One solution is to return the result as a SETOF RECORD.


CREATE OR REPLACE FUNCTION primes( upLim INTEGER ) 
RETURNS SETOF RECORD AS
$$
DECLARE
nums boolean [];
BEGIN

FOR i IN 2..upLim LOOP
nums[i] = true;
END LOOP;
FOR i IN 2..TRUNC(|/upLim) LOOP
IF nums[i] = true
THEN
FOR j IN 2..upLim LOOP
nums[i*j] = false;
EXIT WHEN i * j > upLim;
END LOOP;
END IF;
END LOOP;

FOR i IN 2..upLim LOOP
IF nums[i] = true THEN
RETURN QUERY SELECT i;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;


SELECT * from primes(1000) as foo(prime int);


I'm getting the impression of plpgsql being a very dense topic ... but I'm getting to love it too!

No comments:

Post a Comment