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:
http://sharingtechknowledge.blogspot.com/2012/01/postgresql-foreign-tables-filefdw.html


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:
http://pgxn.org/dist/twitter_fdw/

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 http://t.co/cFgNsQHx - Parallel query processing m... http://t.co/hbfAdytF
 chenghlee    | Parallel query processing may finally be coming to #PostgreSQL http://t.co/TZZ4gM7a
(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