PostgreSQL Perl and a Top N results

Ok , Real Example: check it out at http://www.4rca.com/cgi-bin/top_ten_views.cgi

First Create a function on your postgreSQL database. Note I could pass a parameter to define a top n if I wanted to. This is using the Topics of our PHPBB for my squad. Record count is low but this does scale. You could dump it into a temp table to be used by the function if memory usage becomes an issue. This could be an aggregate function select query you get the same results. Basically the time it takes to run your query is the time it takes to get the results. The added bonus of storing it as a procedure on the database is that the query is already compiled. Also notice how nice the SQL is. Easy to read and maintain. I did modify the HTML tags so that nothing gets totally left off.

CREATE OR REPLACE FUNCTION get_top_posts()
RETURNS SETOF record AS
$$
DECLARE
results record;
record_counter int;
all_user varchar;
all_topic varchar;
all_views int;

BEGIN

record_counter := 0;
all_user := 'Other Users';
all_topic := 'Other Topics';
all_views := 0;


for results in EXECUTE 'select usr.username,top.topic_title, top.topic_views as views
from
phpbb_topics top
, phpbb_users usr
where
top.topic_poster = usr.user_id
order by top.topic_views desc;' LOOP

if record_counter < 9 then
record_counter := record_counter + 1;
Return Next results;
else
record_counter := record_counter + 1;
all_views := all_views + results.views;
end if;

END LOOP;
results := (all_user, all_topic, all_views);
Return Next results;

END
$$
LANGUAGE 'plpgsql' VOLATILE;


This is the stripped down version of the perl script. You can use what ever you want now to submit the query request. You just have to be able to receive the data. Here I’m dumping it out straight. It could be thrown into a data structor and then a “Pie Chart” displayed.


#!/usr/bin/perl

use DBI;

$user="user";
$password="password";
$dbname="databasename";
$host="host address that you are wanting to connect to";

$dsn="dbi:Pg:dbname=$dbname;host=$host;port=5432";
$dbh=DBI->connect("$dsn","$user","$password") or
die print "can't connect $! \n";

$sql = "select * from get_top_posts() AS (users varchar , topic varchar, views int )";

$sth=$dbh->prepare($sql) or
die "can't prepare: $sql \n";

$sth->execute or
die "can't execute: $sql \n";

print "Content-type: text html \n\n";
print "body";
print "table";
print "tr td Users /td td Topics /td td Views /td /tr ";

while (@row=$sth->fetchrow_array)
{
$users = $row[0];
$topic = $row[1];
$views = $row[2];

print "tr td $users /td td $topic /td td $views /td /tr ";
}
print "/table";
print "/body";


It’s a rough Hack, but it works. It’s not supposed to be pretty. You can click on the image and go to the main site and navigate to the forums. Make the numbers change if you like.