Embedded Objects Question

[viv84]

Hi
Using PostgREST v10.2.
I have 2 embedded objects of the same type associated with an API, meaning the API has 2 ids to the same remote table via FK.
One or the other or both ids will be null.
Rather than returning the 2 embedded objects in a query (one null, the other possibly non-null) I'd like to have PostgREST return only the one which is non-null. So in the PostgreSQL view I set the id using coalesce(id1, id2). But I get a PGRST200 error.

Now if I start without doing the coalesce() call and always return both embedded objects it works where I get one null object and one populated.
Then if I add the coalesce() call to make sure both ids' are the same (either both null or both non-null and the same) it works in that the same object is returned for both ids.
But if I then reload the schema cache (SIGUSR1), I get the PGRST200 error.

Basically I'd like my API view to set a single id using coalesce() and have the single associated embedded object be returned so that my API does not have to define a property for both ids since at least one is always null.
Is it possible?

[laurenceisla]

Hi. So, is it something like:

YourTable
- id1 (fk to TableX)
- id2 (fk to TableX)

And one or the other will be null? I think that this can be solved by using Computed Relationships. You can add the COALESCE(id1,id2) to the WHERE filter inside the function.

Try it out to see if it works, if you need help building it, share a simple DDL example to reproduce the issue.