--- getfeed.sql	2007/03/15 10:26:22	1
+++ getfeed.sql	2008/03/15 21:57:40	5
@@ -1,4 +1,4 @@
-create type blog as (feed text, title text, content text, pubdate timestamptz, link text);
+create type blog as (feed text, title text, content text, pubdate timestamptz, author text, link text);
 
 create or replace function getfeed(text)
 returns setof blog
@@ -10,13 +10,21 @@
 
 	my $feed = XML::Feed->parse(URI->new($uri)) or die XML::Feed->errstr;
 
+	sub strip_html {
+		my $t = shift;
+		$t =~ s/<\/?[^>]+>//gs;
+		$t =~ s/\s\s+/ /gs;
+		return $t;
+	}
+
 	for my $entry ($feed->entries) {
 		return_next({
-			feed => $feed->title,
-			title => $entry->title,
-			content => $entry->content->body,
-			link => $entry->link,
-			pubdate => $entry->issued,
+			feed	=> $feed->title,
+			title	=> $entry->title,
+			content	=> strip_html( $entry->content->body ),
+			link	=> $entry->link,
+			pubdate	=> $entry->issued,
+			author	=> $entry->author,
 		});
 	}
 
@@ -24,15 +32,23 @@
 $$;
 
 create view my_feeds as
-select feed,title,substr(content,0,80),pubdate,link from getfeed('http://blog.rot13.org/index.xml')
+select feed,author,title,content,pubdate,link from getfeed('http://blog.rot13.org/index.xml')
 union
-select feed,title,substr(content,0,80),pubdate,link from getfeed('http://saturn.ffzg.hr/noauth/feed/workspace/rot13?category=Recent%20Changes;type=Atom')
+select feed,author,title,content,pubdate,link from getfeed('http://saturn.ffzg.hr/noauth/feed/workspace/rot13?category=Recent%20Changes;type=Atom')
 ;
 
 -- if your terminal isn't iso-8859-2, change this!
 set client_encoding  = 'iso-8859-2';
 
+-- materialize view
 select *
+into feeds
 from my_feeds
+;
+
+select
+	feed,author,title,substr(content,0,80),pubdate,link
+from feeds
 order by pubdate desc
-limit 10;
+limit 30
+;