Re: per-column generic option

(2011/06/17 8:44), David Fetter wrote:
> Sorry not to respond sooner.
>
> First, the per-column generic options are a great thing for us to
> have. :)

Thanks for the comments. :-)

> I have an idea I've been using for the next release of DBI-Link that
> has varying levels of data type mapping. In general, these mappings
> would be units of executable code, one in-bound, and one out-bound,
> for each of:
>
> Universe (everything, default "mapping" is the identity map, i.e. a no-op)
> Database type (e.g. MySQL)
> Instance (e.g. mysql://foo.bar.com:5432)
> Database
> Schema
> Table
> Column

Some of them seem to be able to be mapped to FDW object, e.g. Database
to SERVER and Table to FOREIGN TABLE.

> I didn't include row in the hierarchy because I couldn't think of a
> way to identify rows across DBMSs and stable over time.
>
> The finest-grain transformation that's been set would be the one
> actually used.
>
> Here's an example of a non-trivial mapping.
>
> Database type:
> MySQL
> Foreign data type:
> datetime
> PostgreSQL data type:
> timestamptz
> Transformation direction:
> Import
> Transformation:
> CASE
> WHEN DATA = '0000-00-00 00:00:00'
> THEN NULL
> ELSE DATA
> END
>
> Here, I'm making the simplifying assumption that there is a bijective
> mapping between data types.
>
> Is there some way to fit the per-column part of such a mapping into
> this scheme? We'd need to do some dependency tracking in order to be
> able to point to the appropriate code...

IIUC, you are talking about using FDW options as storage of data type
mapping setting, or mapping definition itself, right? If so, a foreign
table needs to be created to use per-column FDW options. Does it suit
to your idea?

BTW, I couldn't get what you mean by "dependency tracking". You mean
the dependency between foreign column and local column? It might
include essence of your idea... Would you explain the detail?

Regards,
--
Shigeru Hanada