Add field_id support for the Airtable Wrapper

[pjmv]

Hi,

Airtable allocates a unique stable field_id to columns in a table. This is useful because the general case is that users can change column names in airtable, so any automated workflows need something that cannot break because someone did not like a label..

It would be great to be able to somehow configure the wrapper to pull the data with these ids.

[burmecia]

field_id can be used when fetching response from Airtable API, but that requires foreign table column also use same filed id as column name, which may not seem user friendly. For example, below is the Airtable API response when returnFieldsByFieldId query parameter turned on,

{
  "records": [
    {
      "id": "recNhvlWSFG4DSH50",
      "createdTime": "2023-07-19T06:37:14.000Z",
      "fields": {
        "fld8mLmhoJJ9lRUTY": 56.9,
        "fldGscu5K3RSKhQm6": "test2",
        "fldHa9so6szcVOsc4": "note2",
        "fldKVLHG9jHYW2eHb": "content2",
        "fldZah8T3vhbw8Skz": "2023-07-20T03:27:56.000Z"
      }
    }
  ]
}

To successfully extract that response to foreign table, it needs the column names are matching with those field id. Thus, the foreign table may look like this,

create foreign table airtable_table (
  fldGscu5K3RSKhQm6 text,
  fldHa9so6szcVOsc4 text,
  fldKVLHG9jHYW2eHb text,
  fld8mLmhoJJ9lRUTY numeric,
  fldZah8T3vhbw8Skz timestamp
)
server airtable_server
options (
  base_id 'appTc3yI68KN6ukZc',
  table_id 'tbltiLinE56l3YKfn'
);

Those column names may not very user friendly, unless you know that's what you definitely need. You can create another view based on this foreign table to give it more user friendly column name like 'name', 'content' and etc., but that needs hardcode the column names and may lost sync when user changed field name in Airtable.

[pjmv]

Hi, thanks a lot for the answer.

Isn't there a flag that we need to set somewhere at server creation or table creation to set the returnFieldsByFieldId param on when querying the airtable API ? If I remember correctly when working with their API it won't work unless specified.

Clearly it is not developer/dba-friendly but i'd argue that going that route is user-friendly because it allows to set " Natural" looking column names and allow users tho modify names to be more accurate/user-oriented without running the risk of breaking everything, all while allowing the dev/dba to have a consistent style and naming scheme throughout his database.