Mounting database as a file

Postgresql offers the ability to query external data. In some scenarios this can be really useful. This called SQL/MED ("SQL Management of External Data").

One of those cool scenarios is when you need to query a file inside code as if it was a database table. To do so you need to have at least postgresql-9.1.

We will use the simplest file format to demonstrate the idea.

Create a CVS file:

Year,Make,Model,Length
1997,Ford,E350,2.34
2000,Mercury,Cougar,2.38

Create a postgresql server:

CREATE SERVER file_server FOREIGN DATA WRAPPER "file_fdw" ;

Now it's the time to create the table:

CREATE FOREIGN TABLE test (
year int,
make text,
model text,
length float
) SERVER file_server
OPTIONS (format 'csv', filename '/tmp/test.txt', header 'true', delimiter ',', null '');
 

CREATE FOREIGN TABLE

Let's try a select:

testdb=# SELECT * from test ;                                                      
 year |  make   | model  | length 
------+---------+--------+--------
 1997 | Ford    | E350   |   2.34
 2000 | Mercury | Cougar |   2.38
(2 rows)

Change a value in the file, and re-run the select. You should see the changes reflected in query results.

Additional Forgein Data Wrapper exists and they are cool. Using them, you can query 'twitter', MySql tables,

LDAP servers.

Additional documentation:

Foreign data wrappers

For those who likes this idea but don't use postgresql, here's a perl utility that may help:

DBIx::FileSystem

Related documents:

http://archive09.linux.com/feature/127055 

https://github.com/bianster/mysqlfs

http://www.nongnu.org/libsqlfs/

http://www.perlmonks.org/?node_id=397814