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
