Re: Postgrest over foreign data wrapper - Mailing list pgsql-general

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