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?