IMPORT SCHEMA - import only tables

[antonioptorres]

Hello,

I was wondering if there is a way to use the "IMPORT FOREIGN SCHEMA" to import only the Tables (so, exclude the views).

Moreover, is there a way to get the remote schema information without having to import it? In a perspective of performance would be a major difference between both approaches.

Thank you!

[laurenz]

No, there is no support in IMPORT FOREIGN SCHEMA for that. I could add an option, but the criterion seems arbitrary to me.

I wonder what the problem is you are trying to solve. If you want to query Oracle metadata, you could define foreign tables on Oracle views like ALL_TAB_COLUMNS and query these.

[antonioptorres]

Well I have some Oracle views that I'm migrating to PostgreSQL that work as follows: They list all tables from a remote database (connecting with a DB-link) and subtract them to the list of tables of my Database. The output is basically the list of tables that exist in my DB but don't exist in the remote one.

When doing this in Postgres with Oracle_fdw, as I didn't find a way to do this directly in the View's script, I am creating a schema and doing the IMPORT FOREIGN SCHEMA.

The issue is that when importing the schema, the views come as tables.

Also, I need to find a dynamic way of doing this, so that every time I check the view it gets the updated list...

[antonioptorres]

If I was not clear, I can try to put it in more simple terms 😅

[antonioptorres]

ALL_TAB_COLUMNS

Ahhh I will try this, it looks to me that it solves my problem 😃

[antonioptorres]

Do you know if this has been done? I'm getting that the table or view does not exist... and I'm using the same user that has permissions to do that in the Oracle DB.

[antonioptorres]

It worked! Thank you so much for the hint ;)