1 |
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); |
2 |
|
|
3 |
create or replace function getfeed(text) |
create or replace function getfeed(text) |
4 |
returns setof blog |
returns setof blog |
17 |
content => $entry->content->body, |
content => $entry->content->body, |
18 |
link => $entry->link, |
link => $entry->link, |
19 |
pubdate => $entry->issued, |
pubdate => $entry->issued, |
20 |
|
author => $entry->author, |
21 |
}); |
}); |
22 |
} |
} |
23 |
|
|
25 |
$$; |
$$; |
26 |
|
|
27 |
create view my_feeds as |
create view my_feeds as |
28 |
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') |
29 |
union |
union |
30 |
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') |
31 |
; |
; |
32 |
|
|
33 |
-- if your terminal isn't iso-8859-2, change this! |
-- if your terminal isn't iso-8859-2, change this! |
34 |
set client_encoding = 'iso-8859-2'; |
set client_encoding = 'iso-8859-2'; |
35 |
|
|
36 |
|
-- materialize view |
37 |
select * |
select * |
38 |
|
into feeds |
39 |
from my_feeds |
from my_feeds |
40 |
|
; |
41 |
|
|
42 |
|
select |
43 |
|
feed,author,title,substr(content,0,50),pubdate,link |
44 |
|
from feeds |
45 |
order by pubdate desc |
order by pubdate desc |
46 |
limit 10; |
limit 30 |
47 |
|
; |