{"id":7281,"date":"2016-03-14T14:04:16","date_gmt":"2016-03-14T13:04:16","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/"},"modified":"2016-03-14T14:04:16","modified_gmt":"2016-03-14T13:04:16","slug":"feeding-blogsrrs-items-directly-into-your-postgresql-database","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/","title":{"rendered":"Feeding blogs\/rrs items directly into your PostgreSQL database"},"content":{"rendered":"<p>There are <a href=\"https:\/\/wiki.postgresql.org\/wiki\/Foreign_data_wrappers\" target=\"_blank\" rel=\"noopener\">many foreign data wrappers<\/a> available for PostgreSQL. One of them can be used to query <a href=\"https:\/\/en.wikipedia.org\/wiki\/Rss\" target=\"_blank\" rel=\"noopener\">rss feeds<\/a>. In this post I&#8217;ll show you how you can feed summary information of a blog into your PostgreSQL database.<\/p>\n<p>The foreign data wrapper you&#8217;ll need to download and install is called <a href=\"http:\/\/pgxn.org\/dist\/multicorn\/\" target=\"_blank\" rel=\"noopener\">multicorn<\/a>. This foreign data wrapper in fact brings more than a rss wrapper which is documented <a href=\"http:\/\/multicorn.readthedocs.org\/en\/latest\/foreign-data-wrappers.html\" target=\"_blank\" rel=\"noopener\">here<\/a>. Installation is quite easy:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@oel7:\/var\/tmp\/ [PG8] unzip multicorn-1.3.2.zip\npostgres@oel7:\/var\/tmp\/ [PG8] cd multicorn-1.3.2\npostgres@oel7:\/var\/tmp\/multicorn-1.3.2\/ [PG8] make\npostgres@oel7:\/var\/tmp\/multicorn-1.3.2\/ [PG8] sudo make install\n<\/pre>\n<p>This should install without any issues:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5001) [postgres] &gt; create extension multicorn;\nCREATE EXTENSION\nTime: 319.934 ms\n(postgres@[local]:50\ni(postgres@[local]:5001) [postgres] &gt; dx multi*\n                                   List of installed extensions\n   Name    | Version | Schema |                            Description\n-----------+---------+--------+-------------------------------------------------------------------\n multicorn | 1.3.2   | public | Multicorn Python bindings for Postgres 9.2.* Foreign Data Wrapper\n(1 row)\n<\/pre>\n<p>As usual we&#8217;ll have to create a <a href=\"http:\/\/www.postgresql.org\/docs\/current\/static\/sql-createserver.html\" target=\"_blank\" rel=\"noopener\">foreign server<\/a> on top of the extension:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nCREATE SERVER rss_srv foreign data wrapper multicorn options (\n    wrapper 'multicorn.rssfdw.RssFdw'\n);\n<\/pre>\n<p>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 <a href=\"https:\/\/en.wikipedia.org\/wiki\/Rss\" target=\"_blank\" rel=\"noopener\">rss item definition<\/a>:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5001) [postgres] &gt; CREATE FOREIGN TABLE planet_postgres_blogs (\n    \"pubDate\" timestamp,\n    description character varying,\n    title character varying,\n    link character varying\n) server rss_srv options (\n    url     'http:\/\/planet.postgresql.org\/rss20.xml'\n);\nCREATE FOREIGN TABLE\n<\/pre>\n<p>And here we go:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n(postgres@[local]:5001) [postgres] &gt; select title from planet_postgres_blogs;\n                                               title                                               \n---------------------------------------------------------------------------------------------------\n Robert Haas: No More Full-Table Vacuums\n Ernst-Georg Schmid: More fun with a integrarelational DBMS: SoilGrids\n Hubert 'depesz' Lubaczewski: Waiting for 9.6 \u2013 Add a generic command progress reporting facility.\n Magnus Hagander: JSON field constraints\n Robins Tharakan: Separate Index Creation from Data Population during pg_restore\n Andrew Dunstan: Json dates, times, and binary data\n Rubens Souza: And Barman 1.6.0 is out!\n Leo Hsu and Regina Obe: Paris OSGEO Code Sprint 2016 Highlights\n Paul Ramsey: Paris Code Sprint, PostGIS Recap\n Shaun M. Thomas: PG Phriday: Being A Tattletale\n Alexander Korotkov: Pg_pathman Beta Release\n Reuven Lerner: Yes, you can master regular expressions!\n solaimurugan vellaipandian: Installing TeamPostgreSQL on  64 bit Ubuntu 14.x OS\n gabrielle roth: PDXPUG: March meeting in two weeks\n Jamey Hanson: Building JSON Documents from Relational Tables\n Szymon Lipi\u0144ski: Loading JSON Files Into PostgreSQL 9.5\n Reuven Lerner: Using regexps in PostgreSQL\n Hubert 'depesz' Lubaczewski: Waiting for 9.6 \u2013 Add new system view, pg_config\n Szymon Lipi\u0144ski: Converting JSON to PostgreSQL values, simply\n Craig Kerstiens: Hands On Postgres Sharding\n Oleg Bartunov: 20-th Anniversary of PostgreSQL in Saint Petersburg !\n Shaun M. Thomas: PG Phriday: Corralling the Hordes\n Reuven Lerner: [Video 452] Jignesh Shah: PostgreSQL and Linux Containers\n solaimurugan vellaipandian: RPostgreSQL Data analytics on PostgreSQL data using R\n Bruce Momjian: The Plan for FDW-Based Sharding\n Gulcin Yildirim: Are we ready for Nordic PGDay?\n Szymon Lipi\u0144ski: Storing Statistics JSON Data in PostgreSQL\n US PostgreSQL Association: PgDay: LFNW!\n Gulcin Yildirim: FLOSS UK Spring Conference\n Chris Travers: A couple annoyances (and solutions) regarding partitioned tables\n(30 rows)\n<\/pre>\n<p>Cool, isn&#8217;t it?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are many foreign data wrappers available for PostgreSQL. One of them can be used to query rss feeds. In this post I&#8217;ll show you how you can feed summary information of a blog into your PostgreSQL database. The foreign data wrapper you&#8217;ll need to download and install is called multicorn. This foreign data wrapper [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-7281","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Feeding blogs\/rrs items directly into your PostgreSQL database - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Feeding blogs\/rrs items directly into your PostgreSQL database\" \/>\n<meta property=\"og:description\" content=\"There are many foreign data wrappers available for PostgreSQL. One of them can be used to query rss feeds. In this post I&#8217;ll show you how you can feed summary information of a blog into your PostgreSQL database. The foreign data wrapper you&#8217;ll need to download and install is called multicorn. This foreign data wrapper [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-03-14T13:04:16+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Feeding blogs\/rrs items directly into your PostgreSQL database\",\"datePublished\":\"2016-03-14T13:04:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/\"},\"wordCount\":140,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/\",\"name\":\"Feeding blogs\/rrs items directly into your PostgreSQL database - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-03-14T13:04:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Feeding blogs\/rrs items directly into your PostgreSQL database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Feeding blogs\/rrs items directly into your PostgreSQL database - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/","og_locale":"en_US","og_type":"article","og_title":"Feeding blogs\/rrs items directly into your PostgreSQL database","og_description":"There are many foreign data wrappers available for PostgreSQL. One of them can be used to query rss feeds. In this post I&#8217;ll show you how you can feed summary information of a blog into your PostgreSQL database. The foreign data wrapper you&#8217;ll need to download and install is called multicorn. This foreign data wrapper [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/","og_site_name":"dbi Blog","article_published_time":"2016-03-14T13:04:16+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Feeding blogs\/rrs items directly into your PostgreSQL database","datePublished":"2016-03-14T13:04:16+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/"},"wordCount":140,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/","url":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/","name":"Feeding blogs\/rrs items directly into your PostgreSQL database - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-03-14T13:04:16+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/feeding-blogsrrs-items-directly-into-your-postgresql-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Feeding blogs\/rrs items directly into your PostgreSQL database"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=7281"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/7281\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=7281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=7281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=7281"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=7281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}