### FDW Setup and Installation

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

This snippet provides the `setup.py` content for packaging the FDW and instructions on how to install it, making it available for PostgreSQL.

```APIDOC
## FDW Packaging and Installation

### Description
This section details how to package your custom Foreign Data Wrapper (FDW) using `setup.py` and install it so that PostgreSQL can utilize it.

### Method
`setup.py install`

### Endpoint
N/A (Local installation script)

### Parameters
None

### Request Example
None

### Response
N/A

### Code
#### setup.py
```python
import subprocess
from setuptools import setup, find_packages, Extension

setup(
  name='myfdw',
  version='0.0.1',
  author='Ronan Dunklau',
  license='Postgresql',
  packages=['myfdw']
)
```

#### Installation Command
```bash
python setup.py install
```

#### Directory Structure
```
.
|-- myfdw/ 
|   `-- __init__.py
`-- setup.py
```
```

--------------------------------

### Install Multicorn2 from pgxn download

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/installation.md

Installs Multicorn2 by downloading the package from pgxn, unzipping it, and then compiling and installing. This method is useful if direct pgxn client installation is not preferred.

```bash
wget multicorn_pgxn_download
unzip multicorn-multicorn_release
cd multicorn-multicorn_release
make && sudo make install
```

--------------------------------

### Install Multicorn2 from source

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/installation.md

Installs Multicorn2 by cloning the repository from GitHub and compiling from the source code. This method provides the latest development version and is suitable for developers.

```bash
git clone git://github.com/pgsql-io/multicorn2.git
cd multicorn2
make && make install
```

--------------------------------

### Install Multicorn2 using pgxn client

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/installation.md

Installs the Multicorn2 extension using the pgxn client. This is a straightforward method for users who have the pgxn client installed and configured.

```bash
pgxn install multicorn
```

--------------------------------

### Compile and Install Multicorn2

Source: https://github.com/pgsql-io/multicorn2/blob/main/README.md

Standard procedure for downloading, compiling, and installing the Multicorn2 extension into a PostgreSQL environment.

```bash
wget https://github.com/pgsql-io/multicorn2/archive/refs/tags/v3.x.tar.gz
tar -xvf v3.x.tar.gz
cd multicorn2-3.x
make
sudo make install
```

--------------------------------

### Create Multicorn FDW Setup

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

This setup.py file is used to package the Python module for the Foreign Data Wrapper. It specifies the package name, version, author, license, and the packages to include.

```python
import subprocess
from setuptools import setup, find_packages, Extension

setup(
  name='myfdw',
  version='0.0.1',
  author='Ronan Dunklau',
  license='Postgresql',
  packages=['myfdw']
)
```

--------------------------------

### Example EXPLAIN Query Output

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Demonstrates how to use the EXPLAIN statement with a Foreign Data Wrapper that implements the 'explain' hook, showing both standard and verbose output.

```sql
-- View the custom explain output
EXPLAIN SELECT * FROM my_table WHERE id = 1;
-- Output:
-- Foreign Scan on my_table
--   Remote query on: external_table
--   Columns requested: id, name, value
--   Filters: 1 conditions

EXPLAIN VERBOSE SELECT * FROM my_table WHERE id = 1;
-- Output includes: - id = 1
```

--------------------------------

### Install Build Dependencies for Multicorn2

Source: https://github.com/pgsql-io/multicorn2/blob/main/README.md

Commands to install necessary build-essential packages and Python development headers on Linux distributions.

```bash
sudo apt install -y build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc
sudo apt install -y python3 python3-dev python3-setuptools python3-pip
```

```bash
sudo yum install -y bison-devel readline-devel libedit-devel zlib-devel openssl-devel bzip2-devel libmxl2 libxslt-devel wget
sudo yum groupinstall -y 'Development Tools'
sudo yum -y install git python3 python3-devel python3-pip
```

--------------------------------

### Configure and Query SQLAlchemy Foreign Data Wrapper

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Demonstrates how to set up a server and foreign tables for MySQL and MSSQL using the SqlAlchemyFdw. Includes examples of CRUD operations and automatic schema discovery.

```sql
CREATE SERVER alchemy_srv FOREIGN DATA WRAPPER multicorn OPTIONS (wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw');
CREATE FOREIGN TABLE mysql_customers (id integer, name varchar, email varchar, created_at timestamp) SERVER alchemy_srv OPTIONS (tablename 'customers', db_url 'mysql://user:password@mysql-host:3306/mydb', primary_key 'id');
SELECT * FROM mysql_customers WHERE email LIKE '%@gmail.com';
INSERT INTO mysql_customers (id, name, email) VALUES (100, 'New User', 'new@example.com');
IMPORT FOREIGN SCHEMA public FROM SERVER alchemy_srv INTO local_schema OPTIONS (db_url 'postgresql://user:pass@remote-host/remotedb');
```

--------------------------------

### Running Local Multicorn2 Tests with Make

Source: https://github.com/pgsql-io/multicorn2/blob/main/README.md

This snippet shows the commands to run local tests for the Multicorn2 extension after setting up the development environment. It includes installing the module and then executing the `easycheck` command, ensuring the PostgreSQL bin directory is in the PATH.

```bash
sudo make install
PATH=$PATH:$(pg_config --bindir) make easycheck
```

--------------------------------

### Create Process Monitoring Foreign Server and Table (SQL)

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

This snippet demonstrates how to create a PostgreSQL foreign server and table to monitor system processes using the multicorn.processfdw.ProcessFdw. It includes example queries to find PostgreSQL processes and identify memory-hungry ones.

```sql
CREATE SERVER process_srv FOREIGN DATA WRAPPER multicorn OPTIONS (
    wrapper 'multicorn.processfdw.ProcessFdw'
);

CREATE FOREIGN TABLE processes (
    pid integer,
    ppid integer,
    name text,
    exe text,
    cmdline text,
    create_time timestamptz,
    status text,
    cwd text,
    uids integer[],
    gids integer[],
    nice integer,
    num_threads integer,
    cpu_percent float,
    memory_percent float,
    memory_info bigint[]
) SERVER process_srv;

SELECT pid, name, cpu_percent, memory_percent
FROM processes
WHERE name = 'postgres';

SELECT pid, name, memory_percent
FROM processes
WHERE memory_percent > 5.0
ORDER BY memory_percent DESC;
```

--------------------------------

### Implementing Schema Import with multicorn2

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Provides an example of implementing the `import_schema` class method in a `ForeignDataWrapper` to enable automatic table discovery and creation from remote schemas. It covers applying restrictions like `LIMIT TO` and `EXCEPT`.

```python
from multicorn import ForeignDataWrapper, TableDefinition, ColumnDefinition

class SchemaImportFdw(ForeignDataWrapper):
    @classmethod
    def import_schema(cls, schema, srv_options, options, restriction_type, restricts):
        """Import tables from a remote schema.

        Args:
            schema: The remote schema name to import
            srv_options: Server-level options from CREATE SERVER
            options: Options from IMPORT FOREIGN SCHEMA statement
            restriction_type: 'limit', 'except', or None
            restricts: List of table names for LIMIT TO or EXCEPT clauses

        Returns:
            List of TableDefinition objects
        """
        tables = []

        # Discover remote tables (example: from a REST API)
        remote_tables = [
            {'name': 'users', 'columns': [('id', 'integer'), ('name', 'varchar'))},
            {'name': 'orders', 'columns': [('id', 'integer'), ('total', 'numeric')]},
        ]

        for table in remote_tables:
            # Apply restrictions
            if restriction_type == 'limit' and table['name'] not in restricts:
                continue
            if restriction_type == 'except' and table['name'] in restricts:
                continue

            # Build table definition
            table_def = TableDefinition(table['name'])
            table_def.options['source_table'] = table['name']

            for col_name, col_type in table['columns']:
                table_def.columns.append(
                    ColumnDefinition(col_name, type_name=col_type)
                )

            tables.append(table_def)

        return tables
```

```sql
-- Import all tables from remote schema
IMPORT FOREIGN SCHEMA remote_schema
FROM SERVER my_server
INTO local_schema;

-- Import only specific tables
IMPORT FOREIGN SCHEMA remote_schema
LIMIT TO (users, orders)
FROM SERVER my_server
INTO local_schema;

-- Import all except certain tables
IMPORT FOREIGN SCHEMA remote_schema
EXCEPT (temp_data, logs)
FROM SERVER my_server
INTO local_schema;
```

--------------------------------

### Custom REST API Foreign Data Wrapper Example (Python)

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

This snippet outlines a complete example of a custom foreign data wrapper for Multicorn2 that integrates with a REST API. It is intended to showcase all features of Multicorn2 for building FDWs that consume external web services.

```python
# A full example demonstrating a REST API foreign data wrapper with all features.
# This is a placeholder for the actual Python code.
```

--------------------------------

### SQL Configuration for REST API FDW

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Demonstrates how to configure PostgreSQL to use the custom REST API Foreign Data Wrapper. This includes creating a foreign server, defining foreign tables that map to API endpoints, and providing connection details like base URL and API key. It also shows example SQL queries for data retrieval and manipulation.

```sql
-- Use the custom REST API FDW
CREATE SERVER api_srv FOREIGN DATA WRAPPER multicorn OPTIONS (
    wrapper 'myfdw.restfdw.RestApiFdw'
);

CREATE FOREIGN TABLE api_users (
    id integer,
    name varchar,
    email varchar,
    created_at timestamp
) SERVER api_srv OPTIONS (
    base_url 'https://api.example.com/v1',
    endpoint 'users',
    api_key 'your-secret-key',
    primary_key 'id'
);

-- Query the REST API
SELECT * FROM api_users WHERE id = 123;

-- Insert via API
INSERT INTO api_users (name, email) VALUES ('Alice', 'alice@example.com');

-- Import all available endpoints
IMPORT FOREIGN SCHEMA api FROM SERVER api_srv INTO public;

```

--------------------------------

### PostgreSQL CREATE FOREIGN TABLE Statement

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

Example SQL statement to create a foreign table using the custom FDW.

```APIDOC
## PostgreSQL CREATE FOREIGN TABLE Statement

### Description
This SQL statement demonstrates how to define a foreign table in PostgreSQL, specifying the FDW wrapper and its options.

### Method
SQL DDL

### Endpoint
N/A (SQL Command)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
None

### Response
Success: Foreign table created.

### SQL Statement
```sql
CREATE FOREIGN TABLE constanttable (
    test character varying,
    test2 character varying
) server multicorn_srv OPTIONS (
    WRAPPER 'myfdw.ConstantForeignDataWrapper'
);
```
```

--------------------------------

### Python REST API FDW Implementation (multicorn2)

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Implements a Foreign Data Wrapper in Python using multicorn2 to interact with REST APIs. It defines methods for connection, data retrieval (GET), data modification (POST, PUT, DELETE), and schema import. Dependencies include 'multicorn' and 'requests'. It handles API key authentication, URL construction, request execution, and error logging to PostgreSQL.

```python
from multicorn import ForeignDataWrapper, TableDefinition, ColumnDefinition
from multicorn.utils import log_to_postgres
import requests
from logging import INFO, WARNING, ERROR

class RestApiFdw(ForeignDataWrapper):
    """A foreign data wrapper for REST APIs."""

    def __init__(self, options, columns):
        super(RestApiFdw, self).__init__(options, columns)
        self.base_url = options.get('base_url')
        self.endpoint = options.get('endpoint', '')
        self.api_key = options.get('api_key', '')
        self.columns = columns
        self._rowid = options.get('primary_key', 'id')

        if not self.base_url:
            log_to_postgres("base_url option is required", ERROR)

    @property
    def rowid_column(self):
        return self._rowid

    def _make_request(self, method, path='', params=None, json=None):
        url = f"{self.base_url}/{self.endpoint}{path}"
        headers = {'Authorization': f'Bearer {self.api_key}'} if self.api_key else {}
        response = requests.request(method, url, params=params, json=json, headers=headers)
        response.raise_for_status()
        return response.json()

    def get_rel_size(self,quals, columns):
        # Estimate: API typically returns paginated results
        return (1000, len(columns) * 50)

    def get_path_keys(self):
        return [((self._rowid,), 1)]

    def execute(self, quals, columns, sortkeys=None, limit=None, offset=None):
        params = {}

        # Convert quals to API query parameters
        for qual in quals:
            if qual.operator == '=':
                params[qual.field_name] = qual.value

        if limit:
            params['limit'] = limit
        if offset:
            params['offset'] = offset

        try:
            data = self._make_request('GET', params=params)
            items = data if isinstance(data, list) else data.get('items', [data])

            for item in items:
                row = {col: item.get(col) for col in columns}
                yield row

        except requests.RequestException as e:
            log_to_postgres(f"API request failed: {e}", WARNING)

    def insert(self, values):
        result = self._make_request('POST', json=values)
        return result

    def update(self, oldvalues, newvalues):
        row_id = oldvalues[self._rowid]
        result = self._make_request('PUT', path=f'/{row_id}', json=newvalues)
        return result

    def delete(self, oldvalues):
        row_id = oldvalues[self._rowid]
        self._make_request('DELETE', path=f'/{row_id}')

    @classmethod
    def import_schema(cls, schema, srv_options, options, restriction_type, restricts):
        # Discover endpoints from API metadata
        tables = []
        endpoints = ['users', 'products', 'orders']

        for endpoint in endpoints:
            if restriction_type == 'limit' and endpoint not in restricts:
                continue
            if restriction_type == 'except' and endpoint in restricts:
                continue

            table_def = TableDefinition(endpoint)
            table_def.options['endpoint'] = endpoint
            table_def.columns = [
                ColumnDefinition('id', type_name='integer'),
                ColumnDefinition('name', type_name='varchar'),
                ColumnDefinition('created_at', type_name='timestamp'),
            ]
            tables.append(table_def)

        return tables

```

--------------------------------

### Logging to PostgreSQL with multicorn2

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Demonstrates how to use the `log_to_postgres` utility from `multicorn.utils` to send log messages to PostgreSQL's logging system at various levels (DEBUG, INFO, WARNING, ERROR, CRITICAL). It shows how to include hints and details for more informative error messages.

```python
from multicorn import ForeignDataWrapper
from multicorn.utils import log_to_postgres
from logging import DEBUG, INFO, WARNING, ERROR, CRITICAL

class LoggingFdw(ForeignDataWrapper):
    def execute(self,quals, columns, sortkeys=None, limit=None, offset=None):
        # DEBUG - only shows with debug logging enabled
        log_to_postgres("Starting query execution", DEBUG)

        # INFO - sends a NOTICE to client and server logs
        log_to_postgres("Processing 1000 records", INFO)

        # WARNING - warning message to client and logs
        log_to_postgres("Deprecated option used", WARNING,
                       hint="Use 'new_option' instead")

        # ERROR - aborts current transaction
        if not self.connection:
            log_to_postgres("Connection failed", ERROR,
                           hint="Check connection settings",
                           detail="Host: localhost, Port: 5432")

        # CRITICAL - terminates the server process (use with extreme caution!)
        # log_to_postgres("Fatal error", CRITICAL)

        yield {'result': 'data'}
```

--------------------------------

### Implement Planner Optimization Methods

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

Methods to assist the PostgreSQL planner in estimating result sizes and path costs.

```python
def get_rel_size(self, quals, columns):
    # Returns (expected_number_of_row, expected_mean_width_of_a_row)
    return (1000, 100)

def get_path_keys(self):
    # Returns list of (column_name, expected_number_of_row)
    return [('id', 1)]
```

--------------------------------

### Handle Query Quals and Column Filtering

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

Demonstrates how to process SQL query conditions and column projections to optimize data fetching.

```python
# Example of received quals from: SELECT * FROM table WHERE test = 'test 2' AND test2 LIKE '%3%';
quals = [Qual('test', '=', 'test 2'), Qual('test', '~~', '3')]

# Example of received columns from: SELECT test, test2 FROM table;
columns = ['test', 'test2']
```

--------------------------------

### SQL Interface for ProcessFdw

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Demonstrates how to define a foreign server and table to access system process data via SQL queries.

```APIDOC
## SQL Interface for ProcessFdw

### Description
This interface allows users to query active system processes directly from PostgreSQL using standard SQL syntax.

### Method
SQL SELECT

### Endpoint
FOREIGN TABLE processes

### Parameters
#### Path Parameters
- N/A

#### Query Parameters
- WHERE (text/integer) - Optional - Filter processes by attributes like name, pid, or memory_percent.

### Request Example
SELECT pid, name, cpu_percent FROM processes WHERE name = 'postgres';

### Response
#### Success Response (200)
- pid (integer) - Process ID
- name (text) - Process name
- cpu_percent (float) - CPU usage percentage
- memory_percent (float) - Memory usage percentage

#### Response Example
{ "pid": 1234, "name": "postgres", "cpu_percent": 0.5, "memory_percent": 2.3 }
```

--------------------------------

### Create Foreign Server with Multicorn Wrapper

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/getting-started.md

This SQL command creates a foreign server instance for multicorn. The 'wrapper' option in the 'OPTIONS' clause specifies the fully-qualified class name of the Python foreign data wrapper to be used, such as 'multicorn.imapfdw.ImapFdw'.

```sql
CREATE SERVER multicorn_imap FOREIGN DATA WRAPPER multicorn
options (
  wrapper 'multicorn.imapfdw.ImapFdw'
);
```

--------------------------------

### Running Subset of Multicorn2 Tests with Nix

Source: https://github.com/pgsql-io/multicorn2/blob/main/README.md

This command allows running a specific subset of the Multicorn2 test suite against a particular version of PostgreSQL and Python. Users can customize the version numbers in the command to target their desired test environment.

```bash
nix build .#testSuites.test_pg18_py312
```

--------------------------------

### Configure and Query RSS Foreign Data Wrapper

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Explains how to parse RSS and Atom feeds into relational table rows using RssFdw. Demonstrates feed configuration and querying latest items.

```sql
CREATE SERVER rss_srv FOREIGN DATA WRAPPER multicorn OPTIONS (wrapper 'multicorn.rssfdw.RssFdw');
CREATE FOREIGN TABLE tech_news ("pubDate" timestamp, title varchar, link varchar, description text, author varchar) SERVER rss_srv OPTIONS (url 'https://news.ycombinator.com/rss', cache_duration '300');
SELECT title, link, "pubDate" FROM tech_news ORDER BY "pubDate" DESC LIMIT 5;
```

--------------------------------

### Implement Query Planner Optimization Methods in Python

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Implement get_rel_size, get_path_keys, can_sort, and can_limit methods in a Python Foreign Data Wrapper to assist PostgreSQL in choosing optimal query plans. These methods help estimate result set size, declare parameterized paths, specify sort order support, and indicate LIMIT/OFFSET pushdown capabilities.

```python
from multicorn import ForeignDataWrapper, SortKey

class OptimizedFdw(ForeignDataWrapper):
    def get_rel_size(self,quals, columns):
        """Estimate result set size for query planning.

        Args:
            quals: List of Qual objects for the query
            columns: List of columns being requested

        Returns:
            Tuple of (estimated_rows, average_row_width_in_bytes)
        """
        estimated_rows = 1000000
        # Reduce estimate based on filters
        for qual in quals:
            if qual.field_name == 'id' and qual.operator == '=':
                estimated_rows = 1  # Unique lookup
        row_width = len(columns) * 100
        return (estimated_rows, row_width)

    def get_path_keys(self):
        """Declare parameterized paths for index-like access.

        Returns:
            List of (key_columns_tuple, expected_rows) tuples
        """
        return [
            (('id',), 1),      # Lookup by id returns 1 row
            (('email',), 1),   # Lookup by email returns 1 row
            (('name',), 100),  # Lookup by name returns ~100 rows
        ]

    def can_sort(self, sortkeys):
        """Declare which sort orders the FDW can enforce.

        Args:
            sortkeys: List of SortKey namedtuples with:
                - attname: Column name
                - attnum: Column position
                - is_reversed: True if DESC
                - nulls_first: True if NULLS FIRST
                - collate: Collation name

        Returns:
            List of SortKey objects that FDW will enforce
        """
        # Only support sorting by 'id' and 'created_at'
        supported = []
        for key in sortkeys:
            if key.attname in ('id', 'created_at'):
                supported.append(key)
            else:
                break  # Must be cumulative
        return supported

    def can_limit(self, limit, offset):
        """Declare if FDW supports LIMIT/OFFSET pushdown.

        Args:
            limit: The LIMIT value or None
            offset: The OFFSET value or None

        Returns:
            True if FDW can handle both limit and offset
        """
        return True  # This FDW supports limit/offset pushdown

    def execute(self, quals, columns, sortkeys=None, limit=None, offset=None):
        """Execute with pushed-down sort and limit."""
        # sortkeys contains only the keys returned by can_sort
        # limit/offset are set only if can_limit returned True
        query = "SELECT * FROM remote_table"
        if sortkeys:
            order_clauses = []
            for key in sortkeys:
                direction = "DESC" if key.is_reversed else "ASC"
                order_clauses.append(f"{key.attname} {direction}")
            query += " ORDER BY " + ", ".join(order_clauses)
        if limit is not None:
            query += f" LIMIT {limit}"
        if offset is not None:
            query += f" OFFSET {offset}"
        # Execute and yield results
        yield {'id': 1, 'name': 'result'}
```

--------------------------------

### Running All Multicorn2 Tests with Nix

Source: https://github.com/pgsql-io/multicorn2/blob/main/README.md

This command utilizes the Nix package manager to execute a complete regression test suite against all supported versions of Python and PostgreSQL for the Multicorn2 extension. This process can be time-consuming on the first run due to the need to build specific PostgreSQL versions with Python support.

```bash
nix build .#allTestSuites
```

--------------------------------

### Logging to PostgreSQL

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Demonstrates how to use the `log_to_postgres` utility to send messages to PostgreSQL's logging system with different severity levels.

```APIDOC
## Logging to PostgreSQL

Use `log_to_postgres` from the utils module to send messages to PostgreSQL's logging system.

```python
from multicorn import ForeignDataWrapper
from multicorn.utils import log_to_postgres
from logging import DEBUG, INFO, WARNING, ERROR, CRITICAL

class LoggingFdw(ForeignDataWrapper):
    def execute(self,quals, columns, sortkeys=None, limit=None, offset=None):
        # DEBUG - only shows with debug logging enabled
        log_to_postgres("Starting query execution", DEBUG)

        # INFO - sends a NOTICE to client and server logs
        log_to_postgres("Processing 1000 records", INFO)

        # WARNING - warning message to client and logs
        log_to_postgres("Deprecated option used", WARNING,
                       hint="Use 'new_option' instead")

        # ERROR - aborts current transaction
        if not self.connection:
            log_to_postgres("Connection failed", ERROR,
                           hint="Check connection settings",
                           detail="Host: localhost, Port: 5432")

        # CRITICAL - terminates the server process (use with extreme caution!)
        # log_to_postgres("Fatal error", CRITICAL)

        yield {'result': 'data'}
```
```

--------------------------------

### SQL for Writable FDW Operations

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Demonstrates SQL commands for creating a writable foreign table, inserting, updating, and deleting data using a Multicorn2 FDW.

```SQL
-- Create writable foreign table
CREATE FOREIGN TABLE writable_table (
    id integer,
    name varchar,
    value numeric
) SERVER my_server OPTIONS (wrapper 'mymodule.WritableFdw');

-- Insert data
INSERT INTO writable_table VALUES (1, 'test', 100.50);
INSERT INTO writable_table (id, name) VALUES (2, 'another') RETURNING *;

-- Update data
UPDATE writable_table SET value = 200.00 WHERE id = 1;

-- Delete data
DELETE FROM writable_table WHERE id = 2;

```

--------------------------------

### EXPLAIN Hook

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Implement the `explain` method to provide custom output for EXPLAIN queries, offering insights into how the FDW handles queries.

```APIDOC
## EXPLAIN Hook

Implement the `explain` method to provide custom output for EXPLAIN queries.

### `explain`

Provide custom EXPLAIN output.

- **Parameters**:
  - `quals` (List) - List of Qual objects for the query.
  - `columns` (List) - List of columns being requested.
  - `sortkeys` (List, optional) - Keys returned by `can_sort`.
  - `verbose` (bool, optional) - True if EXPLAIN VERBOSE was used.
  - `limit` (int, optional) - The LIMIT value.
  - `offset` (int, optional) - The OFFSET value.
- **Returns**:
  - Iterable of strings to display in EXPLAIN output.

### Example Usage

```sql
-- View the custom explain output
EXPLAIN SELECT * FROM my_table WHERE id = 1;
-- Output:
-- Foreign Scan on my_table
--   Remote query on: external_table
--   Columns requested: id, name, value
--   Filters: 1 conditions

EXPLAIN VERBOSE SELECT * FROM my_table WHERE id = 1;
-- Output includes: - id = 1
```
```

--------------------------------

### Implement Multicorn2 Write API Methods

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

Defines the mandatory primary key configuration and the optional CRUD methods for a Foreign Data Wrapper.

```python
class MyFDW(ForeignDataWrapper):
    def __init__(self, fdw_options, fdw_columns):
        self.rowid_column = fdw_columns.keys()[0]

    def insert(self, new_values):
        pass

    def update(self, old_values, new_values):
        pass

    def delete(self, old_values):
        pass
```

--------------------------------

### Implement Custom Foreign Data Wrapper

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Demonstrates how to inherit from the ForeignDataWrapper base class to create a custom FDW. The implementation requires defining the __init__ method for configuration and the execute method to yield data rows.

```python
from multicorn import ForeignDataWrapper

class MyCustomFdw(ForeignDataWrapper):
    """A simple FDW that returns static data."""

    def __init__(self, options, columns):
        super(MyCustomFdw, self).__init__(options, columns)
        self.columns = columns
        self.data_source = options.get('source', 'default')

    def execute(self, quals, columns, sortkeys=None, limit=None, offset=None):
        for i in range(20):
            row = {}
            for column_name in self.columns:
                row[column_name] = f'{column_name}_{i}'
            yield row
```

--------------------------------

### Import Foreign Schema

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Explains how to implement the `import_schema` class method to enable automatic table discovery and creation from remote schemas.

```APIDOC
## IMPORT FOREIGN SCHEMA

Implement `import_schema` class method to support automatic table discovery and creation from remote schemas.

```python
from multicorn import ForeignDataWrapper, TableDefinition, ColumnDefinition

class SchemaImportFdw(ForeignDataWrapper):
    @classmethod
    def import_schema(cls, schema, srv_options, options, restriction_type, restricts):
        """Import tables from a remote schema.

        Args:
            schema: The remote schema name to import
            srv_options: Server-level options from CREATE SERVER
            options: Options from IMPORT FOREIGN SCHEMA statement
            restriction_type: 'limit', 'except', or None
            restricts: List of table names for LIMIT TO or EXCEPT clauses

        Returns:
            List of TableDefinition objects
        """
        tables = []

        # Discover remote tables (example: from a REST API)
        remote_tables = [
            {'name': 'users', 'columns': [('id', 'integer'), ('name', 'varchar'))},
            {'name': 'orders', 'columns': [('id', 'integer'), ('total', 'numeric'))},
        ]

        for table in remote_tables:
            # Apply restrictions
            if restriction_type == 'limit' and table['name'] not in restricts:
                continue
            if restriction_type == 'except' and table['name'] in restricts:
                continue

            # Build table definition
            table_def = TableDefinition(table['name'])
            table_def.options['source_table'] = table['name']

            for col_name, col_type in table['columns']:
                table_def.columns.append(
                    ColumnDefinition(col_name, type_name=col_type)
                )

            tables.append(table_def)

        return tables
```

```sql
-- Import all tables from remote schema
IMPORT FOREIGN SCHEMA remote_schema
FROM SERVER my_server
INTO local_schema;

-- Import only specific tables
IMPORT FOREIGN SCHEMA remote_schema
LIMIT TO (users, orders)
FROM SERVER my_server
INTO local_schema;

-- Import all except certain tables
IMPORT FOREIGN SCHEMA remote_schema
EXCEPT (temp_data, logs)
FROM SERVER my_server
INTO local_schema;
```
```

--------------------------------

### Using CSV Foreign Data Wrapper with multicorn2

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Illustrates how to use the built-in `CsvFdw` to read data from CSV files as if they were PostgreSQL tables. It covers creating a server wrapper for CSV access and defining a foreign table, including options for filename, header skipping, delimiters, and quote characters.

```sql
-- Create server for CSV access
CREATE SERVER csv_srv FOREIGN DATA WRAPPER multicorn OPTIONS (
    wrapper 'multicorn.csvfdw.CsvFdw'
);

-- Create foreign table mapping to CSV file
CREATE FOREIGN TABLE products (
    product_id numeric,
    name varchar,
    price numeric,
    category varchar
) SERVER csv_srv OPTIONS (
    filename '/var/data/products.csv',
    skip_header '1',
    delimiter ',',
    quotechar '"'
);

-- Query the CSV file like a regular table
SELECT name, price FROM products WHERE category = 'Electronics' ORDER BY price;

-- Results:
--     name     | price
-- -------------+-------
--  USB Cable   |  9.99
--  Headphones  | 49.99
--  Keyboard    | 79.99
```

--------------------------------

### Handle Query Filtering with Quals

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Shows how to process Qual objects within the execute method to filter data at the source. It demonstrates handling standard operators and list-based operators like ANY and ALL.

```python
from multicorn import ForeignDataWrapper, Qual, ANY, ALL

class FilteringFdw(ForeignDataWrapper):
    def execute(self, quals, columns, sortkeys=None, limit=None, offset=None):
        for qual in quals:
            if qual.is_list_operator:
                if qual.list_any_or_all == ANY:
                    pass
                elif qual.list_any_or_all == ALL:
                    pass
            print(f"Filter: {qual.field_name} {qual.operator} {qual.value}")
        yield {'id': 1, 'name': 'filtered_result'}
```

--------------------------------

### Query Planner Optimization Methods

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Implement methods to help PostgreSQL choose optimal query plans by providing estimates and capabilities for sorting and limiting.

```APIDOC
## Query Planner Optimization Methods

Implement `get_rel_size`, `get_path_keys`, `can_sort`, and `can_limit` to help PostgreSQL choose optimal query plans.

### `get_rel_size`

Estimate result set size for query planning.

- **Parameters**:
  - `quals` (List) - List of Qual objects for the query
  - `columns` (List) - List of columns being requested
- **Returns**:
  - Tuple of (estimated_rows, average_row_width_in_bytes)

### `get_path_keys`

Declare parameterized paths for index-like access.

- **Returns**:
  - List of (key_columns_tuple, expected_rows) tuples

### `can_sort`

Declare which sort orders the FDW can enforce.

- **Parameters**:
  - `sortkeys` (List) - List of SortKey namedtuples with column details.
- **Returns**:
  - List of SortKey objects that FDW will enforce.

### `can_limit`

Declare if FDW supports LIMIT/OFFSET pushdown.

- **Parameters**:
  - `limit` (int or None) - The LIMIT value or None
  - `offset` (int or None) - The OFFSET value or None
- **Returns**:
  - Boolean: True if FDW can handle both limit and offset.

### `execute`

Execute the query with pushed-down sort and limit.

- **Parameters**:
  - `quals` (List) - List of Qual objects for the query
  - `columns` (List) - List of columns being requested
  - `sortkeys` (List, optional) - Keys returned by `can_sort`.
  - `limit` (int, optional) - The LIMIT value.
  - `offset` (int, optional) - The OFFSET value.
- **Yields**:
  - Dictionary representing a row of results.
```

--------------------------------

### Implement EXPLAIN Hook in Python

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Implement the 'explain' method in a Python Foreign Data Wrapper to provide custom output for EXPLAIN queries. This allows for detailed insights into how the FDW handles query execution, including filters and requested columns.

```python
from multicorn import ForeignDataWrapper

class ExplainableFdw(ForeignDataWrapper):
    def explain(self,quals, columns, sortkeys=None, verbose=False, limit=None, offset=None):
        """Provide custom EXPLAIN output.

        Args:
            quals, columns, sortkeys, limit, offset: Same as execute()
            verbose: True if EXPLAIN VERBOSE was used

        Returns:
            Iterable of strings to display in EXPLAIN output
        """
        lines = []
        lines.append(f"Remote query on: {self.remote_table}")
        lines.append(f"Columns requested: {', '.join(columns)}")
        if quals:
            lines.append(f"Filters: {len(quals)} conditions")
        if verbose:
            for qual in quals:
                lines.append(f"  - {qual}")
        return lines
```

--------------------------------

### Configure and Query LDAP Foreign Data Wrapper

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Shows how to map LDAP/Active Directory attributes to PostgreSQL tables using LdapFdw. It covers querying user objects and group memberships.

```sql
CREATE SERVER ldap_srv FOREIGN DATA WRAPPER multicorn OPTIONS (wrapper 'multicorn.ldapfdw.LdapFdw');
CREATE FOREIGN TABLE ad_users (dn varchar, cn varchar, mail varchar, memberOf varchar[], department varchar) SERVER ldap_srv OPTIONS (uri 'ldap://ad.example.com:389', path 'ou=Users,dc=example,dc=com', scope 'sub', objectClass 'person', binddn 'cn=readonly,dc=example,dc=com', bindpwd 'readonly_password', pagesize '500');
SELECT cn, mail FROM ad_users WHERE department = 'Engineering';
```

--------------------------------

### Configure and Query IMAP Foreign Data Wrapper

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Provides instructions for accessing email via IMAP using ImapFdw. Includes secure credential management via user mapping and server-side filtering.

```sql
CREATE SERVER imap_srv FOREIGN DATA WRAPPER multicorn OPTIONS (wrapper 'multicorn.imapfdw.ImapFdw');
CREATE USER MAPPING FOR current_user SERVER imap_srv OPTIONS (login 'user@example.com', password 'app_password');
CREATE FOREIGN TABLE emails ("Message-ID" varchar, "From" varchar, "To" varchar, "Subject" varchar, "Date" varchar, flags varchar[], payload text) SERVER imap_srv OPTIONS (host 'imap.gmail.com', port '993', ssl 'True', folder 'INBOX', payload_column 'payload', flags_column 'flags');
SELECT "From", "Subject", "Date" FROM emails WHERE "From" LIKE '%@company.com%' LIMIT 10;
```

--------------------------------

### Create Multicorn Extension in PostgreSQL

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/getting-started.md

This SQL command creates the multicorn extension in your target PostgreSQL database. It requires superuser privileges. This is the first step before using any multicorn functionality.

```sql
CREATE EXTENSION multicorn;
```

--------------------------------

### Implement Transaction Hooks

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

Methods to handle transaction lifecycle events within a Multicorn2 FDW.

```python
def commit(self):
    pass

def rollback(self):
    pass

def pre_commit(self):
    pass
```

--------------------------------

### Transaction Hooks Implementation in Python

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Implements transaction lifecycle methods for a Foreign Data Wrapper (FDW) using Python and Multicorn. These hooks (begin, pre_commit, commit, rollback, etc.) allow the FDW to coordinate with PostgreSQL's transaction management for data consistency.

```Python
from multicorn import ForeignDataWrapper

class TransactionalFdw(ForeignDataWrapper):
    def __init__(self, options, columns):
        super(TransactionalFdw, self).__init__(options, columns)
        self.pending_changes = []
        self.connection = None

    def begin(self, serializable):
        """Called at the beginning of a transaction.

        Args:
            serializable: True if transaction is SERIALIZABLE isolation level
        """
        self.pending_changes = []
        # Start remote transaction

    def pre_commit(self):
        """Called just before COMMIT. Raise exception here to abort."""
        # Attempt to commit to remote system
        # If remote commit fails, raise an exception to abort local transaction
        try:
            self._flush_changes()
        except Exception as e:
            raise Exception(f"Remote commit failed: {e}")

    def commit(self):
        """Called at COMMIT time (use pre_commit for PostgreSQL >= 9.3)."""
        pass

    def rollback(self):
        """Called when transaction is rolled back."""
        self.pending_changes = []
        # Rollback remote transaction

    def sub_begin(self, level):
        """Called at SAVEPOINT creation."""
        pass

    def sub_commit(self, level):
        """Called when SAVEPOINT is released."""
        pass

    def sub_rollback(self, level):
        """Called when ROLLBACK TO SAVEPOINT is executed."""
        pass

    def _flush_changes(self):
        # Apply pending_changes to remote system
        pass

```

--------------------------------

### Constant Table FDW Implementation

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

This snippet shows the Python code for a Foreign Data Wrapper that creates a table with a fixed set of 20 rows. Each cell contains the column name concatenated with the row index.

```APIDOC
## Python FDW Implementation

### Description
This Python class implements a Foreign Data Wrapper (FDW) using the `multicorn.ForeignDataWrapper` interface. It generates a table with 20 rows, where each column's value is a string composed of the column name and the current row index.

### Method
`execute(self, quals, columns)`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```python
# No direct request body for this FDW execution, but it's called by PostgreSQL.
```

### Response
#### Success Response (Yields rows)
- **line** (dict) - A dictionary representing a row, where keys are column names and values are the generated strings.

#### Response Example
```json
{
  "test": "test 0",
  "test2": "test2 0"
}
```

### Code
```python
from multicorn import ForeignDataWrapper

class ConstantForeignDataWrapper(ForeignDataWrapper):

    def __init__(self, options, columns):
        super(ConstantForeignDataWrapper, self).__init__(options, columns)
        self.columns = columns

    def execute(self, quals, columns):
        for index in range(20):
            line = {}
            for column_name in self.columns:
                line[column_name] = '%s %s' % (column_name, index)
            yield line
```
```

--------------------------------

### Implement ConstantForeignDataWrapper

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/implementing-tutorial.md

This Python class implements a Foreign Data Wrapper that generates a fixed set of 20 rows. Each row contains column names concatenated with the row index. It inherits from multicorn.ForeignDataWrapper and implements the __init__ and execute methods.

```python
from multicorn import ForeignDataWrapper

class ConstantForeignDataWrapper(ForeignDataWrapper):

    def __init__(self, options, columns):
        super(ConstantForeignDataWrapper, self).__init__(options, columns)
        self.columns = columns

    def execute(self, quals, columns):
        for index in range(20):
            line = {}
            for column_name in self.columns:
                line[column_name] = '%s %s' % (column_name, index)
            yield line
```

--------------------------------

### Define ImapFdw Foreign Server for Multicorn2

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/multicorn.md

This SQL command specifically creates a foreign server for the Imap FDW using Multicorn2. It sets the WRAPPER option to 'multicorn.imapfdw.ImapFdw', pointing to the Python implementation.

```sql
CREATE SERVER multicorn_imap FOREIGN DATA WRAPPER multicorn
    OPTIONS (
    WRAPPER 'multicorn.imapfdw.ImapFdw'
);
```

--------------------------------

### Enable Multicorn Extension in PostgreSQL

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

This SQL command enables the Multicorn extension within a PostgreSQL database. It requires superuser privileges to execute.

```sql
-- Enable the Multicorn extension (requires superuser)
CREATE EXTENSION multicorn;
```

--------------------------------

### SQLAlchemy Foreign Data Wrapper

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Connect to any database supported by SQLAlchemy with full read/write and transaction support.

```APIDOC
## SQLAlchemy Foreign Data Wrapper

### Description
Connect to any database supported by SQLAlchemy with full read/write and transaction support.

### Method
N/A (SQL commands)

### Endpoint
N/A

### Parameters
N/A

### Request Example
```sql
-- Create server for SQLAlchemy access
CREATE SERVER alchemy_srv FOREIGN DATA WRAPPER multicorn OPTIONS (
    wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
);

-- Connect to MySQL database
CREATE FOREIGN TABLE mysql_customers (
    id integer,
    name varchar,
    email varchar,
    created_at timestamp
) SERVER alchemy_srv OPTIONS (
    tablename 'customers',
    db_url 'mysql://user:password@mysql-host:3306/mydb',
    primary_key 'id'
);

-- Connect to Microsoft SQL Server
CREATE FOREIGN TABLE mssql_orders (
    order_id integer,
    customer_id integer,
    total numeric,
    status varchar
) SERVER alchemy_srv OPTIONS (
    tablename 'orders',
    schema 'sales',
    drivername 'mssql',
    host 'sqlserver.example.com',
    database 'OrderDB',
    username 'app_user',
    password 'secret',
    primary_key 'order_id'
);

-- Query with automatic qual pushdown
SELECT * FROM mysql_customers WHERE email LIKE '%@gmail.com';

-- Write operations (requires primary_key option)
INSERT INTO mysql_customers (id, name, email) VALUES (100, 'New User', 'new@example.com');
UPDATE mysql_customers SET name = 'Updated Name' WHERE id = 100;
DELETE FROM mysql_customers WHERE id = 100;

-- IMPORT FOREIGN SCHEMA to auto-discover tables
IMPORT FOREIGN SCHEMA public
FROM SERVER alchemy_srv
INTO local_schema
OPTIONS (db_url 'postgresql://user:pass@remote-host/remotedb');
```

### Response
N/A (SQL execution results)
```

--------------------------------

### Define Foreign Server and Table

Source: https://context7.com/pgsql-io/multicorn2/llms.txt

Configures a foreign server pointing to a specific Python class and maps a foreign table to that server. This allows querying external data as if it were a standard PostgreSQL table.

```sql
-- Create a server pointing to your custom FDW class
CREATE SERVER my_custom_server FOREIGN DATA WRAPPER multicorn OPTIONS (
    wrapper 'mymodule.MyCustomFdw'
);

-- Create a foreign table using the server
CREATE FOREIGN TABLE my_foreign_table (
    id integer,
    name varchar,
    value numeric
) SERVER my_custom_server OPTIONS (
    source 'production'
);

-- Query the foreign table like any regular table
SELECT * FROM my_foreign_table WHERE id > 10;
```

--------------------------------

### Create Imap Foreign Table with Multicorn2

Source: https://github.com/pgsql-io/multicorn2/blob/main/doc/multicorn.md

This SQL statement creates a foreign table named 'gmail' that connects to an IMAP server via the Multicorn2 extension. It defines the table schema and provides necessary connection and authentication options for the Imap FDW.

```sql
CREATE FOREIGN TABLE gmail (
    "Message-ID" character varying,
    "From" character varying,
    "Subject" character varying,
    "payload" character varying,
    "flags" character varying[],
    "To" character varying) SERVER multicorn_imap OPTIONS (
        host 'imap.gmail.com',
        port '465',
        payload_column 'payload',
        flags_column 'flags',
        ssl 'True',
        login 'mylogin',
        password 'mypassword'
);
```