Feeding blogs/rrs items directly into your PostgreSQL database

There are many foreign data wrappers available for PostgreSQL. One of them can be used to query rss feeds. In this post I’ll show you how you can feed summary information of a blog into your PostgreSQL database.

The foreign data wrapper you’ll need to download and install is called multicorn. This foreign data wrapper in fact brings more than a rss wrapper which is documented here. Installation is quite easy:

postgres@oel7:/var/tmp/ [PG8] unzip multicorn-1.3.2.zip
postgres@oel7:/var/tmp/ [PG8] cd multicorn-1.3.2
postgres@oel7:/var/tmp/multicorn-1.3.2/ [PG8] make
postgres@oel7:/var/tmp/multicorn-1.3.2/ [PG8] sudo make install

This should install without any issues:

(postgres@[local]:5001) [postgres] > create extension multicorn;
CREATE EXTENSION
Time: 319.934 ms
(postgres@[local]:50
i(postgres@[local]:5001) [postgres] > dx multi*
                                   List of installed extensions
   Name    | Version | Schema |                            Description
-----------+---------+--------+-------------------------------------------------------------------
 multicorn | 1.3.2   | public | Multicorn Python bindings for Postgres 9.2.* Foreign Data Wrapper
(1 row)

As usual we’ll have to create a foreign server on top of the extension:

CREATE SERVER rss_srv foreign data wrapper multicorn options (
    wrapper 'multicorn.rssfdw.RssFdw'
);

All we need to do from now on is to create a foreign table which will hold the summary information. The column names must much the rss item definition:

(postgres@[local]:5001) [postgres] > CREATE FOREIGN TABLE planet_postgres_blogs (
    "pubDate" timestamp,
    description character varying,
    title character varying,
    link character varying
) server rss_srv options (
    url     'http://planet.postgresql.org/rss20.xml'
);
CREATE FOREIGN TABLE

And here we go:

(postgres@[local]:5001) [postgres] > select title from planet_postgres_blogs;
                                               title                                               
---------------------------------------------------------------------------------------------------
 Robert Haas: No More Full-Table Vacuums
 Ernst-Georg Schmid: More fun with a integrarelational DBMS: SoilGrids
 Hubert 'depesz' Lubaczewski: Waiting for 9.6 – Add a generic command progress reporting facility.
 Magnus Hagander: JSON field constraints
 Robins Tharakan: Separate Index Creation from Data Population during pg_restore
 Andrew Dunstan: Json dates, times, and binary data
 Rubens Souza: And Barman 1.6.0 is out!
 Leo Hsu and Regina Obe: Paris OSGEO Code Sprint 2016 Highlights
 Paul Ramsey: Paris Code Sprint, PostGIS Recap
 Shaun M. Thomas: PG Phriday: Being A Tattletale
 Alexander Korotkov: Pg_pathman Beta Release
 Reuven Lerner: Yes, you can master regular expressions!
 solaimurugan vellaipandian: Installing TeamPostgreSQL on  64 bit Ubuntu 14.x OS
 gabrielle roth: PDXPUG: March meeting in two weeks
 Jamey Hanson: Building JSON Documents from Relational Tables
 Szymon Lipiński: Loading JSON Files Into PostgreSQL 9.5
 Reuven Lerner: Using regexps in PostgreSQL
 Hubert 'depesz' Lubaczewski: Waiting for 9.6 – Add new system view, pg_config
 Szymon Lipiński: Converting JSON to PostgreSQL values, simply
 Craig Kerstiens: Hands On Postgres Sharding
 Oleg Bartunov: 20-th Anniversary of PostgreSQL in Saint Petersburg !
 Shaun M. Thomas: PG Phriday: Corralling the Hordes
 Reuven Lerner: [Video 452] Jignesh Shah: PostgreSQL and Linux Containers
 solaimurugan vellaipandian: RPostgreSQL Data analytics on PostgreSQL data using R
 Bruce Momjian: The Plan for FDW-Based Sharding
 Gulcin Yildirim: Are we ready for Nordic PGDay?
 Szymon Lipiński: Storing Statistics JSON Data in PostgreSQL
 US PostgreSQL Association: PgDay: LFNW!
 Gulcin Yildirim: FLOSS UK Spring Conference
 Chris Travers: A couple annoyances (and solutions) regarding partitioned tables
(30 rows)

Cool, isn’t it?