Re: Postgrest over foreign data wrapper

Ah. I didn't realize Postgrest was something, rather than just a typo. An
fkey to a foreign table is not supported.

Related-
https://dba.stackexchange.com/questions/138591/foreign-key-references-constraint-on-postgresql-foreign-data-wrapper

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

On Wed, Feb 13, 2019 at 2:43 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(at)hotmail(dot)fr
> > <mailto: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 <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(dot)klaver(at)aklaver(dot)com
>