Postgrest: PATCH fails with self-referencing entity

[jpincas]

Trying to work out whether this is a bug or me being an idiot.

Given an entity like:

create table website_category (
    id uuid primary key default uuid_generate_v1 ()
    , name text
    , parent_category_id uuid references website_category(id)
);

A GET request like /website_category?id=eq.a9b44532-5cf6-11e9-bad0-0242c0a8f008&select=parent_category(id,name) works perfectly, returning the nested parent category as expected.

However, the same PATCH, lets say with body {"name": "new name"}, fails with:

{"message":"Could not find foreign keys between these entities, No relation found between pg_source and parent_category"}

This is the first time I've seen this and my app has tonnes of these types of calls that work fine. This is the first entity I have that references itself, so I'm wondering if that is tripping up Postgrest.

[steve-chavez]

@jpincas Are you trying to do a PATCH and then return related data?

We have tests that covers this for POST, but not for PATCH.
https://github.com/PostgREST/postgrest/blob/28b3d6cafdc24df7d085ca84eaa8de8b4094be56/test/Feature/InsertSpec.hs#L71-L79

[steve-chavez]

I can reproduce, even POST fails:

curl -X POST "localhost:3000/website_category?id=eq.a9b44532-5cf6-11e9-bad0-0242c0a8f008&select=parent_category(id,name)" -d '{"name": "ff"}'

{"message":"Could not find foreign keys between these entities, No relation found between pg_source and parent_category"} 

[jpincas]

Seems to only be a problem with self-referencing entities though mind you. I have a lot of both POSTs and PATCHES that are returning related data and they always seem to work fine, which kind of blew me away first time I saw it work. Even more mind boggling to me was that even calling a custom function with Postgrest and asking for related data on the return entity just worked perfectly out of the box!! Ah, if the world only knew about Postgrest....

[steve-chavez]

@jpincas Fixed this one in #1391. Also added docs references for Embedding on Stored Procedures and Embedding after Insertions/Updates/Deletions(any correction is welcomed :smiley:).