Re: SQL/MED - core functionality

2010/12/13 Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp>:
> On Sun, 12 Dec 2010 23:47:53 +0200
> Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
>> On ons, 2010-12-01 at 12:30 +0900, Hitoshi Harada wrote:
>> > From a user's view, this is very long way to see a simplest foreign
>> > table. I know it is based on the standard, but I really want a
>> > shortcut. Especially, I don't understand why CREATE USER MAPPING FOR
>> > current_user SERVER <server> is needed for default use case. If you
>> > forget CREATE USER MAPPING and do CREATE FOREIGN TABLE, it raises an
>> > error. User mapping is useful if the local user and remote user should
>> > be mapped but I imagine in most cases they are the same.
>> > postgresql_fdw can tell the remote user by conninfo string, in
>> > addition.
>>
>> I reviewed the standard about this, and a lot of things are
>> implementation-defined.  I think user mappings could be made optional.
>
> Simple FDWs such as File FDW might not have concept of "user" on
> remote side.  In such case, it would be enough to control access
> privilege per local user with GRANT/REVOKE SELECT statement.

Yeah, with file_fdw users won't understand why CREATE USER MAPPING is needed.

>> > This is another topic, but it would be useful if CREATE FOREIGN TABLE
>> > can omit column definitions since fdw usually knows what should be
>> > there in the definitions. I some times mistyped the column names
>> > between remote and local and resulted in fail on execution.
>>
>> Also, according to the standard, the column list in CREATE FOREIGN TABLE
>> is optional (if you can get it in some automatic way, of course).
>
> To allow omitting column definitions for that purpose, a way to create
> ero-column tables would have to be provided.  New syntax which allows
> FDWs to determine column definition would be necessary.
>
> ex)
> -- Create foo from the remote table foo on the server bar
> CREATE FOREIGN TABLE foo SERVER bar;
> -- Create zero-column table foo
> CREATE FOREIGN TABLE foo () SERVER bar;
>
> To support this feature, another hook function need to be added to FDW
> API.  ISTM that this feature should be considered with IMPORT SCHEMA
> statement.

Hmm, the benefit from it does not seem so much as the paid cost.
Definition of minimum APIs sound like the first step to get it in the
next release. We could add it later.

Regards,

--
Hitoshi Harada