Re: per-column generic option

(2011/07/04 10:17), Shigeru Hanada wrote:
> (2011/07/03 18:50), Kohei KaiGai wrote:
>> I checked the per-column generic option patch.
>> Right now, I have nothing to comment on anymore.
>> So, it should be reviewed by committers.
>
> Thanks for the review!.

I would like to propose adding force_not_null support to file_fdw, as
the first use case of per-column FDW option. Attached patch, which
assumes that per_column_option_v3.patch has been applied, implements
force_not_null option as per-column FDW option.

Overview
========
This option is originally supported by COPY FROM command, so I think
it's reasonable to support it in file_fdw too. It would provides more
flexible parsing capability. In fact, this option has been supported
by the internal routines which are shared with COPY FROM, but currently
we don't have any way to specify it.

Difference between COPY
=======================
For COPY FROM, FORCE_NOT_NULL is specified as a list of column names
('*' is not allowed). For file_fdw, per-table FDW option can be used
like other options, but then file_fdw needs parser which can identify
valid column. I think it's too much work, so I prefer per-column FDW
option which accepts boolean value string. The value 'true' means that
the column doesn't be matched against NULL string, same as ones listed
for COPY FROM.

Example:

If you have created a foreign table with:

CREATE FOREIGN TABLE foo (
c1 int OPTIONS (force_not_null 'false'),
c2 text OPTIONS (force_not_null 'true')
) SERVER file OPTIONS (file '/path/to/file', format 'csv', null '');

values which are read from the file for c1 are matched against
null-representation-string '', but values for c2 are NOT. Empty strings
for c2 are stored as empty strings; they don't treated as NULL value.

Regards,
--
Shigeru Hanada