Creating virtual view above multiple schemas(not public) in Postgres

[tamarm]

Hi,

I am using Teiid-spring-boot starter in order to expose OData4 in my spring boot app.

My app is a microservice, used to aggregate relevant data from multiple Postgres dbs and expose it with OData.

I don't want to configure multiple data sources to all those Postgres tables. I configured single datasource, connecting to my own Postgres db. 

Therefore I am using Postgres FDW in order to access other Postgres dbs and import their schemas into my Postgres db.

I created postgres view conains all the relevant data from the different imported schemas in my db.

Then I couldn't define primary key on Postgres view (as I already asked in:Teiid does not present view as EntityType  ) So I defined Teiid View(using Java JPA class) in order to have primary key(as you suggested).

Now my app is working as expected, but with double view definition - one "real" view in Postgres, and one virtual view in Teiid(defind in @SelectQuery).

So I tried to remove the "real" view definition.

The "real" view definition, creates the Postgres view in public schema.

without this view, I should access db's schemas which are not public.

I tried all the possible combinations but every time I got this error: Group does not exist

How should I define a query inside a @SelectQuery, which access to table(let's call it 't') inside a schema(not the public schema! let's call it 's') inside a database defined as datasource(let's call it 'd')?

[rareddy]

you can add a property in application.properties

spring.datasource.{source-name}.importer.schemaPattern={schema-name}

[tamarm]

Thanks Ramesh, but I want to create the virtual view from multiple schemas(which non of them is the "public" schema)

Is it posible?

[rareddy]

yes, you can define multiple data sources with the same credentials one for each schema you want to build. So, using the above property define two different data sources for each schema.

Then using the @SelectQuery construct define a view that joins the tables from multiple schemas. Note to qualify the names of the tables using their data source name. For ex: if you have two data sources A and B, then

@SelectQuery("select x, y,z from A.foo join B.bar on A.foo.x = B.bar.z")

If you are familiar with the traditional way of defining the VDB in -vdb.xml I recently added a feature to deploy that VDB in spring-boot if you want to take that route. However, IMO above is much simpler for a java developer.