IMPORT FOREIGN SCHEMA

[vpicavet]

It would be nice to implement an import foreign schema for various datasources.
For each dataset it would create the metadata and the data tables.

On problem for this is that we need to have pointcloud_formats populated before creating the data table. And we want to generate the pcid schema thanks to the metadata FDW.
Ways to workaround :

• before giving the table schema back, we execute an insert on pointcloud_formats
• hide this behind a SQL or PL/PgSQL function which does the insert and then import the foreign schema

This could lead to some unnecessary elements in pointcloud_formats whenever the import foreign schema is rollbacked, but there does not seem to be a better way, and this is not that annoying.

[mbredif]

I propose the following loosely coupled solution :

1. having the fdw expose a metadata foreign table named "pointcloud_formats", with columns ("pcid", "srid" and "schema") that contains all the rows that have to be inserted in pgpointcloud's "pointcloud_formats" table. The pcid would there be the foreign pcid, to be referenced by the columns of the foreign tables. This table only could be imported with an import foreign schema with a limit clause.
2. have a function that compares the local and foreign "pointcloud_formats" schemas and srids, that would retrieve the mapping from the foreign pcids to the local pcid, possibly after insertion if no local match is found.
3. have the import foreign schema and the create foreign table commands accept as an option a mapping from the foreign pcid to the local pcid.

The whole process could finally be wrapped into a function if required, but that is just for convenience.
As a side note, addressing pgpointcloud/pointcloud#95 might help us here in 2.

[elemoine]

Questions regarding your proposal:

In 2. you mention having a function that would handle the mappings between the local and foreign "pointcloud_formats" tables. How/where would you define that function? fdw-li3ds is not a Postgres extension, so it doesn't have an SQL file. We could still define that function in an SQL file, and refer to it in the documentation. Have you envisioned something fancier than that?

In 3. you mention passing pcid mappings to import foreign schema and create foreign table. Do you know if complex option values are possible?

Thanks.

[mbredif]

2. nothing more, I was counting on you :)
3. good point. Anyhow, if only a string is tolerated, on could specify this mapping using a comma-separated list of integers, if we restrict foreign pcids to be contiguous.

[elemoine]

Closing this for now.