Re: [PATCH] postgres-fdw: column option to override foreign types

On Mon, Mar 1, 2021 at 12:59 PM Dian M Fay <dian(dot)m(dot)fay(at)gmail(dot)com> wrote:
>
> Full use of a custom data type with postgres_fdw currently requires the
> type be maintained in both the local and remote databases. `CREATE
> FOREIGN TABLE` does not check declared types against the remote table,
> but declaring e.g. a remote enum to be local text works only partway, as
> seen here. A simple select query against alpha_items returns the enum
> values as text; however, *filtering* on the column yields an error.
>
> create database alpha;
> create database beta;
>
> \c alpha
>
> create type itemtype as enum ('one', 'two', 'three');
> create table items (
> id serial not null primary key,
> type itemtype not null
> );
> insert into items (type) values ('one'), ('one'), ('two');
>
> \c beta
>
> create extension postgres_fdw;
> create server alpha foreign data wrapper postgres_fdw options (dbname 'alpha', host 'localhost', port '5432');
> create user mapping for postgres server alpha options (user 'postgres');
>
> create foreign table alpha_items (
> id int,
> type text
> ) server alpha options (table_name 'items');

postgres_fdw assumes that the local type declared is semantically same
as the remote type. Ideally the enum should also be declared locally
and used to declare type's datatype. See how to handle UDTs in
postgres_fdw at
https://stackoverflow.com/questions/37734170/can-the-foreign-data-wrapper-fdw-postgres-handle-the-geometry-data-type-of-postg

--
Best Wishes,
Ashutosh Bapat