TDS foreign data wrapper (tds_fdw) lets you connect Postgres to databases that use the Tabular Data Stream (TDS) protocol, such as Sybase databases and Microsoft SQL Server. It uses the FreeTDS library to implement the DB-Library interface.
Architecture overview
TDS foreign data wrapper provides an interface between a TDS-compatible database server (such as Microsoft SQL Server or Sybase) and a Postgres database. It transforms Postgres statements (SELECT) into queries that are understood by the remote TDS database, using the FreeTDS library to implement the DB-Library interface.
The wrapper supports WHERE clause and column push downs, which means filters and column selections are evaluated on the remote server rather than locally.
Key features
These are the key features of TDS foreign data wrapper.
WHERE clause pushdown
TDS foreign data wrapper supports pushdown of the WHERE clause. Filter conditions are evaluated on the remote TDS server rather than fetching all rows locally.
For more information, see Foreign table options.
Column pushdown
TDS foreign data wrapper supports column pushdown. Only the columns required by the query are fetched from the remote server, reducing data transfer.
For more information, see Foreign table options.
Import foreign schema
TDS foreign data wrapper supports IMPORT FOREIGN SCHEMA, which lets you import an entire remote schema rather than defining each foreign table manually.
For more information, see Import a foreign schema.
FreeTDS connection pooling
TDS foreign data wrapper establishes a connection to a foreign server during the first query that uses a foreign table associated with the foreign server. This connection is kept and reused for subsequent queries in the same session.
Automated cleanup
TDS foreign data wrapper allows the cleanup of foreign tables and server objects using the DROP EXTENSION command:
DROP EXTENSION tds_fdw CASCADE;
Flexible remote execution
TDS foreign data wrapper supports both table-based and query-based foreign tables. You can define a foreign table that maps to a remote table or view, or one that executes a custom SQL query on the remote server.
For more information, see Create a foreign table.