airtable-fdw 0.2.0

Airtable Multicorn FDW for Postgres

Airtable Foreign Data Wrapper

Usage

Ensure multicorn is loaded and define Foreign Data Wrapper for airtable

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

Define table as

create foreign table schema.table_name (
    "_id" varchar options (rowid 'true'), -- column used as rowid, may be any name, 
    -- should appear only one
    "Some text column" varchar,
    "Some numeric column" numeric,
    "Some date column" date,
    "Some complex column" json, -- best for complex fields
    "Some json nullable column" json options (nulljson 'true'), -- keep nulls as json ('null'::json instead of null::json)
    "Some computed column" varchar options (computed) , -- 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
    );

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.

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": "[e-mail]", "name":"My Name"} will be converted to (someid,[e-mail],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