Re: foreign data wrapper option manipulation during Create foreign table time?

Le mercredi 29 octobre 2014 14:16:23 Demai Ni a écrit :
> Robert and Ronan,
>
> many thanks for your response.
>
> I realized there is no clean way/api for it. maybe a hacking of ptree can
> do the trick.. :-)
>
> I will also take a look at IMPORT FOREIGN SCHEMA. However, for this
> requirement, I still need the user to input filename or filefolder, and I'd
> like to process the file(s) during create foreign table time, and save the
> processed result somewhere like ftoptions column in pg_foreign_table. may
> be some other way I can save the process result and make it assessable
> during query time?

You can pass options to IMPORT FOREIGN SCHEMA. So, you could maybe implement
it so that the user can do that:

IMPORT FOREIGN SCHEMA public FROM SERVER file_server INTO local_schema OPTIONS
(filename '/path/to/the/file');

Your FDW would then issue several CREATE FOREIGN TABLE statements, with all
the necessary options.

Or even, to allow importing multiple files at once:

IMPORT FOREIGN SCHEMA public FROM SERVER file_server INTO local_schema OPTIONS
(directory '/path/to/the/file_dir/');

>
> Demai
>
> On Wed, Oct 29, 2014 at 10:01 AM, Ronan Dunklau <ronan(dot)dunklau(at)dalibo(dot)com>
>
> wrote:
> > Le mercredi 29 octobre 2014 12:49:12 Robert Haas a écrit :
> > > On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni <nidmgg(at)gmail(dot)com> wrote:
> > > > I am looking for a couple pointers here about fdw, and how to change
> >
> > the
> >
> > > > option values during CREATE table time.
> > > >
> > > > I am using postgres-xc-1.2.1 right now. For example, it contains
> >
> > file_fdw,
> >
> > > > whose create-table-stmt looks like:
> > > > CREATE FOREIGN TABLE t1(....)
> > > > SERVER file_server
> > > > OPTIONS(format 'text',filename 'testing.txt');
> > > >
> > > > I would like to replace the 'testing.txt' with absolute path like
> > > > '/user/testor1/testing.txt', and make sure the new value is saved in
> > > > pg_foreign_table; the file_fdw_validator is used to validate the
> >
> > options,
> >
> > > > but is there a way to replace the optionValue here? And get the new
> >
> > value
> >
> > > > stored in pg_foreign_table?
> > > >
> > > > Thanks
> > > >
> > > > BTW, in my real use case, I am trying to interpret a hdfs file and
> >
> > would
> >
> > > > need to save some hostname/port information in the option value, which
> >
> > not
> >
> > > > necessary specified by user.
> > >
> > > I don't think there's going to be a clean way to do this. The
> > > intention of the system is that the user-provided options are simply
> > > stored, not that the FDW author is going to use the options list to
> > > store their own bits and pieces.
> >
> > I would do that during the IMPORT FOREIGN SCHEMA statement. That way, the
> > user
> > doesn't have to specify those options: they would be generated at IMPORT
> > time,
> > and the user could change them later if really needed.
> >
> > --
> > Ronan Dunklau
> > http://dalibo.com - http://dalibo.org

--
Ronan Dunklau
http://dalibo.com - http://dalibo.org