Foreign Data Wrappers

I’ve never used PostgreSQL until now. But recently I learned about PostgreSQL having implemented SQL/MED, which allows to query and update other data sources through the PostgreSQL server and this by issuing regular SQL statements. You can access CSV-Files, MongoDB, Twitter, WebServices, etc.

Is this possible with the Xojo built-in PostgreSQL database class or would I have to go through the PostgreSQL C API?

FDW has some requirementson the server side
But after you set those up you should not need any thing else
The whole point of FDW is to make it mostly invisible

Thanks a lot. Took me only 10 minutes to get it working. It’s fantastic - one can read CSV files without problems. And it’s fast: it reads a 10 column CSV file with 50.000 records in under 1 second. Going to try MongoDB next.

If anyone is interested, this is the code for reading a CSV file:

[code]Dim db As New PostgreSQLDatabase()

// code to connect to db omitted

db.SQLExecute(“DROP EXTENSION IF EXISTS file_fdw;”)
db.SQLExecute(“CREATE EXTENSION file_fdw;”)

db.SQLExecute(“DROP SERVER IF EXISTS Addresses;”)
db.SQLExecute(“CREATE SERVER Addresses FOREIGN DATA WRAPPER file_fdw;”)

db.SQLExecute( _
"CREATE FOREIGN TABLE Addresses ( first_name text, last_name text, …, city text ) " + _
"SERVER Addresses " + _
“OPTIONS ( FILENAME ‘/Library/PostgreSQL/Addresses.csv’, FORMAT ‘csv’, HEADER ‘true’ );” _

Dim rst As RecordSet = db.SQLSelect(“SELECT * FROM Addresses”)[/code]
Note that the file must be in a folder accessible by the PostgreSQL server, not by the user logged in the OS. For this test I moved the file into the PostgreSQL folder (this is on OS X), which is /Library/PostgreSQL.

1 Like