Resource embedding on views that don't select keys

[bcb]

• PostgreSQL version: postgres:13 from docker hub
• PostgREST version: posgrest/postgrest:v8.0.0 from docker hub

I have the following two views, which obscure the ids:

create view api.teams as
select id_encode(id, 'salt', 8) as hash_id, name, speaker from data.team;

create view api.members as
select id_encode(id, 'salt', 8) as hash_id, name, headline, photo from data.member;

Attempting to use resource embedding:

$ curl 'http://localhost:8000/rest/teams?select=members(*)'
{"hint":"If a new foreign key between these entities was created in the database, try reloading the schema cache.","message":"Could not find a relationship between teams and members in the schema cache"}%

Resource embedding only works if the keys are selected, the primary key in the teams view, and foreign key in the members view. However I don't want to reveal these ids.

[wolfgangwalther]

So you want to join those two views on the hash_id column?

I see two ways of dealing with this:

• Either create a hash_id <type> GENERATED ALWAYS AS (id_encode(id, 'salt', 8)) STORED column on both tables, then add indexes and the foreign key relationship to those. This will make embedding work, because you will only select the simple column hash_id from the base table.
• Or create a computed column for custom embedding as mentioned here: Ability to generate queries with distinct/group by #915 (comment). Note, that you'd very likely still need an index on the id_encode expression, to make the query efficient.

[bcb]

Thanks, the first one sounds like a good solution. But I’m curious why Postgrest can only see the relationship if the columns are selected?

[wolfgangwalther]

We are parsing the view definition during "schema load" and need to identify the columns of the base table, to be able to tell whether there is any FK associated. Since FKs can't be set on the function expression, but just directly on the column, we need to identify the column - and also join on the column. If the bare column is not exposed on the view, we have nothing to join on. We don't want to join on the expression, because we are very likely not using an efficient query when doing so.