Re: Postgrest over foreign data wrapper

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?

*Michael Lewis | Software Engineer*
*Entrata*
*c: **619.370.8697 <619-370-8697>*

On Wed, Feb 13, 2019 at 2:04 PM adrien ruffie <adriennolarsen(at)hotmail(dot)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, 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
>>
>