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@xxxxxxxxxx
<mailto:adriennolarsen@xxxxxxxxxx>> 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@xxxxxxxxxxx
