3 Replies Latest reply on Jan 10, 2019 10:30 AM by Ramesh Reddy

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

    Tamar Meron Newbie

      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')?