Re: per-column generic option

On Tue, Jun 14, 2011 at 05:56:05PM +0900, Shigeru Hanada wrote:
> Hi,
>
> I would like to propose support for per-column generic option, which
> is defined in the SQL/MED standard. In 9.0 release, support for
> foreign tables and per-table generic option have been added, but
> support for per-column generic option hasn't.
>
> Please examine the description below and attached patch
> per_column_option_v1.patch. Any comments or questions are welcome.

Sorry not to respond sooner.

First, the per-column generic options are a great thing for us to
have. :)

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

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...

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate