Thread: Postgrest over foreign data wrapper
we have a tricky problem with my colleague.We have to database db1 and db2 linked by a foreign data wrapper connection.1 table "contractline" in db1 and "contract" in db2.We use postgrest in order to request db2 via CURL.But we want to add a link between previous tables.In db2 we have a foreign table ft_contractlineexample:Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description--------------+--------------------------+-----------+----------+---------+-------------+----------+--------------+-------------id | character varying(1024) | | not null | | | extended | |ccode | text | | | | | extended | |status | text | | | | | extended | |We want to add for example, the following constraint:ALTER TABLE contract ADD CONSTRAINT contractline_ft_contract_fkey FOREIGN KEY (contractid) REFERENCES ft_contractline(ccode);in order to use the following query (via CURL on db2):SELECT c.name, c.id FROM contract c JOIN ft_contractline ft_c ON c.id = ft_c.ccode WHERE c.type = 'business'but we saw, isn't possible to add a foreign key on 'contract' table of db2 to 'ft_contractline' foreign table ...Do you know way to do it ?Thank all and best regards.Adrien
On 2/13/19 1:04 PM, adrien ruffie wrote: > we have a tricky problem with my colleague. > We have to database db1 and db2 linked by a foreign data wrapper > connection. > 1 table "contractline" in db1 and "contract" in db2. > > We use postgrest in order to request db2 via CURL. > But we want to add a link between previous tables. > > In db2 we have a foreign table ft_contractline > example: > > Column | Type | Collation | Nullable | > Default | FDW options | Storage | Stats target | Description > --------------+--------------------------+-----------+----------+---------+-------------+----------+--------------+------------- > id | character varying(1024) | | not null | > | | extended | | > ccode | text | | | > | | extended | | > status | text | | | > | | extended | | > > We want to add for example, the following constraint: > ALTER TABLE contract ADD CONSTRAINT contractline_ft_contract_fkey > FOREIGN KEY (contractid) REFERENCES ft_contractline(ccode); > > in order to use the following query (via CURL on db2): > SELECT c.name <http://c.name>, c.id <http://c.id> FROM contract c > JOIN ft_contractline ft_c ON c.id <http://c.id> = ft_c.ccode WHERE > c.type = 'business' You don't need a FK to use the above query, just to enforce referential integrity between the tables. Do you want RI between the tables? > > but we saw, isn't possible to add a foreign key on 'contract' table > of db2 to 'ft_contractline' foreign table ... > > Do you know way to do it ? > > Thank all and best regards. > > Adrien > -- Adrian Klaver adrian.klaver@aklaver.com
You don't need an fkey to write a select statement with a join. I think I must be missing something. Are you wanting it to enforce integrity across the dblink? Or are you adding an fkey with the assumption that you will get an index?
On Wed, Feb 13, 2019 at 2:04 PM adrien ruffie <adriennolarsen@hotmail.fr> wrote:
we have a tricky problem with my colleague.We have to database db1 and db2 linked by a foreign data wrapper connection.1 table "contractline" in db1 and "contract" in db2.We use postgrest in order to request db2 via CURL.But we want to add a link between previous tables.In db2 we have a foreign table ft_contractlineexample:Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description--------------+--------------------------+-----------+----------+---------+-------------+----------+--------------+-------------id | character varying(1024) | | not null | | | extended | |ccode | text | | | | | extended | |status | text | | | | | extended | |We want to add for example, the following constraint:ALTER TABLE contract ADD CONSTRAINT contractline_ft_contract_fkey FOREIGN KEY (contractid) REFERENCES ft_contractline(ccode);in order to use the following query (via CURL on db2):SELECT c.name, c.id FROM contract c JOIN ft_contractline ft_c ON c.id = ft_c.ccode WHERE c.type = 'business'but we saw, isn't possible to add a foreign key on 'contract' table of db2 to 'ft_contractline' foreign table ...Do you know way to do it ?Thank all and best regards.Adrien
On 2/13/19 1:36 PM, Michael Lewis wrote: > You don't need an fkey to write a select statement with a join. I think > I must be missing something. Are you wanting it to enforce integrity Me to until I looked at this: http://postgrest.org/en/v5.2/api.html#resource-embedding "PostgREST can also detect relations going through join tables. Thus you can request the Actors for Films (which in this case finds the information through Roles). You can also reverse the direction of inclusion, asking for all Directors with each including the list of their Films: GET /directors?select=films(title,year) HTTP/1.1 Important Whenever foreign key relations change in the database schema you must refresh PostgREST’s schema cache to allow resource embedding to work properly. See the section Schema Reloading. " > across the dblink? Or are you adding an fkey with the assumption that > you will get an index? > * > * > * > * > *Michael Lewis | Software Engineer* > *Entrata** > * > *c:**619.370.8697 <tel:619-370-8697>* > > > On Wed, Feb 13, 2019 at 2:04 PM adrien ruffie <adriennolarsen@hotmail.fr > <mailto:adriennolarsen@hotmail.fr>> wrote: > > we have a tricky problem with my colleague. > We have to database db1 and db2 linked by a foreign data wrapper > connection. > 1 table "contractline" in db1 and "contract" in db2. > > We use postgrest in order to request db2 via CURL. > But we want to add a link between previous tables. > > In db2 we have a foreign table ft_contractline > example: > > Column | Type | Collation | Nullable > | Default | FDW options | Storage | Stats target | Description > --------------+--------------------------+-----------+----------+---------+-------------+----------+--------------+------------- > id | character varying(1024) | | not null > | | | extended | | > ccode | text | | | > | | extended | | > status | text | | > | | | extended | | > > We want to add for example, the following constraint: > ALTER TABLE contract ADD CONSTRAINT > contractline_ft_contract_fkey FOREIGN KEY (contractid) > REFERENCES ft_contractline(ccode); > > in order to use the following query (via CURL on db2): > SELECT c.name <http://c.name>, c.id <http://c.id> FROM contract > c JOIN ft_contractline ft_c ON c.id <http://c.id> = ft_c.ccode > WHERE c.type = 'business' > > but we saw, isn't possible to add a foreign key on 'contract' > table of db2 to 'ft_contractline' foreign table ... > > Do you know way to do it ? > > Thank all and best regards. > > Adrien > -- Adrian Klaver adrian.klaver@aklaver.com
Ah. I didn't realize Postgrest was something, rather than just a typo. An fkey to a foreign table is not supported.
Related-
On Wed, Feb 13, 2019 at 2:43 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 2/13/19 1:36 PM, Michael Lewis wrote:
> You don't need an fkey to write a select statement with a join. I think
> I must be missing something. Are you wanting it to enforce integrity
Me to until I looked at this:
http://postgrest.org/en/v5.2/api.html#resource-embedding
"PostgREST can also detect relations going through join tables. Thus you
can request the Actors for Films (which in this case finds the
information through Roles). You can also reverse the direction of
inclusion, asking for all Directors with each including the list of
their Films:
GET /directors?select=films(title,year) HTTP/1.1
Important
Whenever foreign key relations change in the database schema you must
refresh PostgREST’s schema cache to allow resource embedding to work
properly. See the section Schema Reloading.
"
> across the dblink? Or are you adding an fkey with the assumption that
> you will get an index?
> *
> *
> *
> *
> *Michael Lewis | Software Engineer*
> *Entrata**
> *
> *c:**619.370.8697 <tel:619-370-8697>*
>
>
> On Wed, Feb 13, 2019 at 2:04 PM adrien ruffie <adriennolarsen@hotmail.fr
> <mailto:adriennolarsen@hotmail.fr>> wrote:
>
> we have a tricky problem with my colleague.
> We have to database db1 and db2 linked by a foreign data wrapper
> connection.
> 1 table "contractline" in db1 and "contract" in db2.
>
> We use postgrest in order to request db2 via CURL.
> But we want to add a link between previous tables.
>
> In db2 we have a foreign table ft_contractline
> example:
>
> Column | Type | Collation | Nullable
> | Default | FDW options | Storage | Stats target | Description
> --------------+--------------------------+-----------+----------+---------+-------------+----------+--------------+-------------
> id | character varying(1024) | | not null
> | | | extended | |
> ccode | text | | |
> | | extended | |
> status | text | |
> | | | extended | |
>
> We want to add for example, the following constraint:
> ALTER TABLE contract ADD CONSTRAINT
> contractline_ft_contract_fkey FOREIGN KEY (contractid)
> REFERENCES ft_contractline(ccode);
>
> in order to use the following query (via CURL on db2):
> SELECT c.name <http://c.name>, c.id <http://c.id> FROM contract
> c JOIN ft_contractline ft_c ON c.id <http://c.id> = ft_c.ccode
> WHERE c.type = 'business'
>
> but we saw, isn't possible to add a foreign key on 'contract'
> table of db2 to 'ft_contractline' foreign table ...
>
> Do you know way to do it ?
>
> Thank all and best regards.
>
> Adrien
>
--
Adrian Klaver
adrian.klaver@aklaver.com