Sunday, January 8, 2012

Postgresql - Foreign Tables. twitter_fdw. advanced twitter searches

Case scenario:
Postgresql 9.1 on Fedora 15

Objective: find the latest 100 tweets, that talk about postgresql and contain the word 'query' .

My latest experience with this topic:

Prerequisite: Postgresql 9.1 with contrib modules and postgresql9.1-server-devel (or whatever is called in your distro).
Step1. Install the twitter_fdw wrapper:

Step2. Create the extension in the database you'll use for this purpose.
postgres=# CREATE EXTENSION twitter_fdw;

Step3. Fire the query away. (The CREATE EXTENSION statement creates not only FDW handlers but also Data Wrapper, Foreign Server, User Mapping and twitter table) <- isn't that great?

postgres=# SELECT from_user AS user , text::varchar(144) AS message FROM twitter WHERE q = '#postgresql' and text::varchar(144) ilike '%query%';

The result:
user     |                                                                  message                       
--------------+------------------------------------------------------------------------------------------------ -------------------------------------------
 postgresqlbr | Parallel query processing may finally be coming to #PostgreSQL - Parallel query processing m...
 chenghlee    | Parallel query processing may finally be coming to #PostgreSQL
(2 rows)

I didn't like a column named 'text', so I renamed it to 'message' but the thing stopped working (I should have known better --> the mapping! duh), so I switched the change back. I'll define a custom foreign server, user mapping , and foreign table latter just for practice.

No comments:

Post a Comment