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?
Le mercredi 29 octobre 2014 12:49:12 Robert Haas a écrit :
> On Tue, Oct 28, 2014 at 5:26 PM, Demai Ni <nidmgg@gmail.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