/[pg-getfeed]/getfeed.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Annotation of /getfeed.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 5 - (hide annotations)
Sat Mar 15 21:57:40 2008 UTC (11 years, 3 months ago) by dpavlin
File size: 1187 byte(s)
strip html from entry content
1 dpavlin 3 create type blog as (feed text, title text, content text, pubdate timestamptz, author text, link text);
2 dpavlin 1
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 dpavlin 5 sub strip_html {
14     my $t = shift;
15     $t =~ s/<\/?[^>]+>//gs;
16     $t =~ s/\s\s+/ /gs;
17     return $t;
18     }
19    
20 dpavlin 1 for my $entry ($feed->entries) {
21     return_next({
22 dpavlin 4 feed => $feed->title,
23     title => $entry->title,
24 dpavlin 5 content => strip_html( $entry->content->body ),
25 dpavlin 4 link => $entry->link,
26     pubdate => $entry->issued,
27     author => $entry->author,
28 dpavlin 1 });
29     }
30    
31     return undef;
32     $$;
33    
34     create view my_feeds as
35 dpavlin 3 select feed,author,title,content,pubdate,link from getfeed('http://blog.rot13.org/index.xml')
36 dpavlin 1 union
37 dpavlin 3 select feed,author,title,content,pubdate,link from getfeed('http://saturn.ffzg.hr/noauth/feed/workspace/rot13?category=Recent%20Changes;type=Atom')
38 dpavlin 1 ;
39    
40     -- if your terminal isn't iso-8859-2, change this!
41     set client_encoding = 'iso-8859-2';
42    
43 dpavlin 2 -- materialize view
44 dpavlin 1 select *
45 dpavlin 2 into feeds
46 dpavlin 1 from my_feeds
47 dpavlin 2 ;
48    
49     select
50 dpavlin 5 feed,author,title,substr(content,0,80),pubdate,link
51 dpavlin 2 from feeds
52 dpavlin 1 order by pubdate desc
53 dpavlin 2 limit 30
54     ;

  ViewVC Help
Powered by ViewVC 1.1.26