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 |
|
|
; |