Re: Postgrest over foreign data wrapper

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