Line # Revision Author
1 3 dpavlin create type blog as (feed text, title text, content text, pubdate timestamptz, author text, link text);
2 1 dpavlin
3 create or replace function getfeed(text)
4 returns setof blog
5 language plperlu
6 as $$
7 my ($uri) = @_;
8
9 use XML::Feed;
10
11 my $feed = XML::Feed->parse(URI->new($uri)) or die XML::Feed->errstr;
12
13 5 dpavlin sub strip_html {
14 my $t = shift;
15 $t =~ s/<\/?[^>]+>//gs;
16 $t =~ s/\s\s+/ /gs;
17 return $t;
18 }
19
20 1 dpavlin for my $entry ($feed->entries) {
21 return_next({
22 4 dpavlin feed => $feed->title,
23 title => $entry->title,
24 5 dpavlin content => strip_html( $entry->content->body ),
25 4 dpavlin link => $entry->link,
26 pubdate => $entry->issued,
27 author => $entry->author,
28 1 dpavlin });
29 }
30
31 return undef;
32 $$;
33
34 create view my_feeds as
35 3 dpavlin select feed,author,title,content,pubdate,link from getfeed('http://blog.rot13.org/index.xml')
36 1 dpavlin union
37 3 dpavlin select feed,author,title,content,pubdate,link from getfeed('http://saturn.ffzg.hr/noauth/feed/workspace/rot13?category=Recent%20Changes;type=Atom')
38 1 dpavlin ;
39
40 -- if your terminal isn't iso-8859-2, change this!
41 set client_encoding = 'iso-8859-2';
42
43 2 dpavlin -- materialize view
44 1 dpavlin select *
45 2 dpavlin into feeds
46 1 dpavlin from my_feeds
47 2 dpavlin ;
48
49 select
50 5 dpavlin feed,author,title,substr(content,0,80),pubdate,link
51 2 dpavlin from feeds
52 1 dpavlin order by pubdate desc
53 2 dpavlin limit 30
54 ;