SQL WRAPPER

What is SQL WRAPPER?

Defines a Foreign Data Wrapper that lets SQL access external data sources as if they were local tables.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL WRAPPER: PostgreSQL 9.1+, EDB Postgres Advanced Server, Amazon Redshift (limited), IBM Db2 (via nicknames), Standard SQL/MED. Not supported in MySQL, SQL Server, Oracle, or SQLite by default.

SQL WRAPPER Full Explanation

In the SQL/MED portion of the SQL standard and in PostgreSQL, the WRAPPER keyword appears within CREATE FOREIGN DATA WRAPPER. A foreign data wrapper (FDW) is a plug-in module that implements the routines required to read, write, and manage data stored outside the database—such as another relational system, a CSV file, or an API. Once a wrapper is installed, you can create foreign servers and foreign tables that map to the external resource, then run ordinary SELECT, INSERT, UPDATE, or DELETE statements against them. The wrapper abstracts connection handling, data type mapping, and pushdown capabilities. Caveats: each FDW supports its own option set; write support may be partial; performance depends on the external source; SECURITY DEFINER functions used as handlers must be trusted.

SQL WRAPPER Syntax

CREATE FOREIGN DATA WRAPPER wrapper_name
    [ HANDLER handler_function ]
    [ VALIDATOR validator_function ]
    [ OPTIONS ( option 'value' [, ... ] ) ];

ALTER FOREIGN DATA WRAPPER wrapper_name
    [ HANDLER handler_function | NO HANDLER ]
    [ VALIDATOR validator_function | NO VALIDATOR ]
    [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ];

DROP FOREIGN DATA WRAPPER wrapper_name [ CASCADE | RESTRICT ];

SQL WRAPPER Parameters

wrapper_name (identifier) - Name of the foreign data wrapper.
handler_function (regproc) - Function that returns fdw_handler and contains callback routines.
validator_function (regproc) - Function that validates OPTIONS clauses.
OPTIONS (key/value list) - Wrapper specific configuration such as connection strings or timeouts.

Example Queries Using SQL WRAPPER

-- Install the postgres_fdw extension (ships with PostgreSQL)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create a wrapper (usually done by the extension but shown for clarity)
CREATE FOREIGN DATA WRAPPER postgres_fdw;

-- Point to an external PostgreSQL server
CREATE SERVER analytics_srv
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'analytics-db.local', dbname 'analytics', port '5432');

-- Map a local user to the remote user
CREATE USER MAPPING FOR current_user
  SERVER analytics_srv
  OPTIONS (user 'reporting', password 'secret');

-- Expose a remote table
CREATE FOREIGN TABLE public.page_views (
    id bigint,
    user_id int,
    viewed_at timestamptz
) SERVER analytics_srv
  OPTIONS (schema_name 'public', table_name 'page_views');

-- Query it like any other table
SELECT count(*) FROM public.page_views WHERE viewed_at >= current_date - INTERVAL '7 days';

Expected Output Using SQL WRAPPER

Wrapper and associated metadata objects are created in the system catalogs
Subsequent SELECT returns the row count from the external page_views table
If the connection fails or the wrapper handler is misconfigured, an error is raised

Use Cases with SQL WRAPPER

Federate multiple PostgreSQL databases without ETL
Join SaaS application data with internal tables via an HTTP FDW
Prototype migrations by querying the source system directly
Provide read only access to a legacy system while new tables are built

Common Mistakes with SQL WRAPPER

Forgetting to install or load the extension that provides the wrapper
Omitting a HANDLER function when creating a custom wrapper
Using incorrect option names; each wrapper has its own accepted keys
Expecting full pushdown support when the FDW only supports scans

First Introduced In

SQL:2008 (SQL/MED) and PostgreSQL 9.1

Frequently Asked Questions

What is the difference between a wrapper and a server?

A wrapper is the plug-in that implements the FDW API, while a foreign server is a configuration object that points to a specific external source using that wrapper.

How do I install a wrapper in PostgreSQL?

Most wrappers are shipped as extensions. Run CREATE EXTENSION wrapper_name; then the wrapper, handler, and validator objects become available.

Can I secure credentials used by a wrapper?

Yes. Store passwords in a .pgpass file or use PostgreSQL secrets managers, then omit the password option in USER MAPPING to avoid exposing secrets in DDL.

What happens if the external system is down?

Queries against foreign tables will error or hang until they reach the statement timeout. Consider setting connection retries or timeouts in the wrapper options.