How I wasted 10 minutes this morning

So this morning I fired up my feed reader and swung into planetpostgresql where I read Greg’s nifty little post about [http://people.planetpostgresql.org/greg/index.php?/archives/47-Fun-with-prime-numbers.html using plperl to make a list of prime numbers]. It seemed cool, but it lacked that certain something that would unlock the keys to the universe…. and then it clicked. If I crossed [http://www.justatheory.com/computers/databases/postgresql/higher_order_plpgsql.html David Wheelers Fibonacci functions] with Greg’s prime number function, I could generate a list of all Fibonacci numbers that were also prime numbers and that…**THAT** would be power! In order to complete my master plan, I need to do some quick hacking

-bash-3.00$ createdb davinci;

CREATE DATABASE

-bash-3.00$ createlang plperl davinci;

-bash-3.00$ createlang plpgsql davinci;

Odd… no return code for createlang? Oh well… can’t be slowed down by that now!!

-bash-3.00$ psql -U postgres davinci

Welcome to psql 8.1.1, the PostgreSQL interactive terminal.

Type:  copyright for distribution terms

       h for help with SQL commands

       ? for help with psql commands

       g or terminate with semicolon to execute query

       q to quit

davinci=#

I then created Greg’s function “make_primes”…

davinci=#

davinci=# CREATE FUNCTION make_primes(INT)

davinci-# RETURNS SETOF INT

davinci-# LANGUAGE plperl IMMUTABLE AS

davinci-# $$

davinci$# (1 x $_) !~ /^1?$|^(11+?)+$/

davinci$#   and return_next($_) for (1..shift);

davinci$# return;

davinci$# $$;

CREATE FUNCTION

davinci=#

And then went to install David’s “fib_fast” function… but whoops! His function doesn’t actually return a list of Fibonacci numbers, it returns a sum of them. No problem though, a few quick edits later and:

davinci=#

davinci=# CREATE OR REPLACE FUNCTION make_fibs(

davinci(#     fib_for integer

davinci(# ) RETURNS setof integer AS $$

davinci$# DECLARE

davinci$#     ret integer := 0;

davinci$#     nxt integer := 1;

davinci$#     tmp integer;

davinci$# BEGIN

davinci$#     FOR num IN 1..fib_for LOOP

davinci$#         tmp := ret;

davinci$#         ret := nxt;

davinci$#         nxt := tmp + nxt;

davinci$#         IF nxt > fib_for THEN

davinci$#             RETURN;

davinci$#         ELSE

davinci$#             RETURN NEXT nxt;

davinci$#         END IF;

davinci$#     END LOOP;

davinci$#

davinci$#     RETURN;

davinci$# END;

davinci$# $$ LANGUAGE plpgsql;

CREATE FUNCTION

davinci=#

And now… the total mysteries of the universe were at my command:

davinci=# select * from make_fibs(50);

 make_fibs

———–

         1

         2

         3

         5

         8

        13

        21

        34

(8 rows)

davinci=# select * from make_primes(50);

 make_primes

————-

           2

           3

           5

           7

          11

          13

          17

          19

          23

          29

          31

          37

          41

          43

          47

(15 rows)

davinci=# select * from make_fibs(50) intersect select * from make_primes(50);

 make_fibs

———–

         2

         3

         5

        13

(4 rows)

davinci=# select * from make_fibs(100) intersect select * from make_primes(100);

 make_fibs

———–

         2

         3

         5

        13

        89

(5 rows)

Fascinating huh?