airtable-fdw 0.3.4
Airtable Multicorn FDW for Postgres
License: MIT
Requires Python: >=3.7,<4.0

# Airtable Foreign Data Wrapper

## Installation

### Requirements

PostgreSQL 9.1+ with [Multicorn](http://multicorn.org/) extension installed.

### Loading extension and defining FDW server

Ensure multicorn is loaded and define Foreign Data Wrapper for airtable

```postgresql
create extension if not exists multicorn;
create server if not exists multicorn_airtable_srv foreign data wrapper multicorn options (
    wrapper 'airtable_fdw.AirtableFDW'
);
```

## Usage

Define table as

```postgresql
create foreign table schema.table_name (
    "_id" varchar options (rowid 'true'),                       -- column used as rowid, may be any name, 
                                                                -- should appear only onece
    "Some text column" varchar,
    "Some numeric column" numeric,
    "Some date column" date,
    "Some complex column" json,                                 -- can be used for complex fields but see example below 
    "Some json nullable column" json options (nulljson 'true'), -- keep nulls as json ('null'::json instead of null::json)
    "Some computed column" varchar options (computed 'true')    -- column that won't be modified with update
                                                                -- may appear multiple times
) server multicorn_airtable_srv options (
    api_key '...',      -- api access key
    base_key '...',     -- database identifier
    table_name '...',   -- name of table to read from
    view_name '...',    -- optional view name, if not present raw table will be read
    rowid_column '...'  -- optional rowid column name will be used if no column has `rowid` option set 
);
```

If complex column - like `Collaborator` - appears in table it is read from AirTable API as a `json` and could be treated as `json` or as a complex, custom defined type.

```postgresql
create type AirtableCollaborator as
(
    id     varchar,
    email  varchar,
    "name" varchar
);
create foreign table schema.table_name (
    "_id" varchar options (rowid 'true'),
    "editor" AirtableCollaborator options (complextype_fields 'id,email,name', complextype_send 'email')
) server multicorn_airtable_srv options (
    api_key '...',
    base_key '...',
    table_name '...'
);

```

where:
* `complextype_fields 'id,email,name'` indicates how record string should be constructed from `json` - so `{"id": "someid", "email": "me@example.com", "name":"My Name"}` will be converted to `(someid,me@example.com,My Name)` and will be correctly casted to `AirtableCollaborator` type.
* `complextype_send 'email'` means that when this field is modified only `email` field will be sent to API

### Usage Tips

* Use `AND` in `WHERE` clause whenever possible, `OR`s are not handled well (at all?) by *multicorn* so unconditional queries are sent to Airtable (watch the quota!).
* If `OR` is required try to replace it with  `IN (...)`

## Features

* Configurable to read from given base / table / view
* SQL `WHERE` clause transformed to `formula` query (so number of requests to API is optimized)
* Batch `INSERT`/`UPDATE`/`DELETE`
* support for complex types - json is parsed to complex type on read (`SELECT`), and single, selected field is set on write (`INSERT`, `UPDATE`)