/[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 2 - (hide annotations)
Thu Mar 15 10:31:41 2007 UTC (17 years ago) by dpavlin
File size: 1010 byte(s)
create agregated table feeds with all entries
1 dpavlin 1 create type blog as (feed text, title text, content text, pubdate timestamptz, link text);
2    
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     for my $entry ($feed->entries) {
14     return_next({
15     feed => $feed->title,
16     title => $entry->title,
17     content => $entry->content->body,
18     link => $entry->link,
19     pubdate => $entry->issued,
20     });
21     }
22    
23     return undef;
24     $$;
25    
26     create view my_feeds as
27 dpavlin 2 select feed,title,content,pubdate,link from getfeed('http://blog.rot13.org/index.xml')
28 dpavlin 1 union
29 dpavlin 2 select feed,title,content,pubdate,link from getfeed('http://saturn.ffzg.hr/noauth/feed/workspace/rot13?category=Recent%20Changes;type=Atom')
30 dpavlin 1 ;
31    
32     -- if your terminal isn't iso-8859-2, change this!
33     set client_encoding = 'iso-8859-2';
34    
35 dpavlin 2 -- materialize view
36 dpavlin 1 select *
37 dpavlin 2 into feeds
38 dpavlin 1 from my_feeds
39 dpavlin 2 ;
40    
41     select
42     feed,title,substr(content,0,50),pubdate,link
43     from feeds
44 dpavlin 1 order by pubdate desc
45 dpavlin 2 limit 30
46     ;

  ViewVC Help
Powered by ViewVC 1.1.26