Trouble with getting Computed Relationships to work

[MonkOfTheFeels]

Environment

• PostgreSQL version: Docker image postgres:latest
• PostgREST version: Docker image postgrest/postgrest:v11.2.0
• Operating system: Windows 11

Description

I am trying to follow the documentation for computed relationships and when I create relationships per the docs but running into problems when I put the films and premiers tables in a schema rather than public. My postgrest image's PGRST_DB_SCHEMA variable is set to the same schema

When I try to define a computed relationship between films and premieres I get the following respond even after refreshing my schema multiple times. I am able to get the individual tables which were created at the same time as the function.

End point

    GET /films?select=title,premieres(*)

Response

{
    "code": "PGRST200",
    "details": "Searched for a foreign key relationship between 'films' and 'premieres' in the schema 'workflow', but no matches were found.",
    "hint": null,
    "message": "Could not find a relationship between 'films' and 'premieres' in the schema cache"
}

I have been trying to debug this but can't seem to see what I have done wrong. I am sure this is human error and was just seeing if anyone knows what could be causing it.

Below is SQL I am using, Are computed relationships only allowed on foregin tables per the example in the docs?

CREATE SCHEMA workflow;

CREATE TABLE workflow.films (
	id int,
	title VARCHAR,
	
	PRIMARY KEY (id)
);


create TABLE workflow.premieres (
  id integer,
  location text,
  "date" date,
  film_id integer
)

CREATE function workflow.premieres(workflow.films) returns setof workflow.premieres as $$
  select * from workflow.premieres where film_id = $1.id
$$ stable language sql;

GRANT SELECT ON workflow.premieres TO admin_user;
GRANT SELECT ON workflow.films TO admin_user;

GRANT EXECUTE ON FUNCTION workflow.premieres(workflow.films) TO admin_user;

[laurenceisla]

Are computed relationships only allowed on foregin tables per the example in the docs?

No, they are very useful for foreign tables but not exclusive to them.

...even after refreshing my schema multiple times

Your example works for me... maybe it's related to #2524 where NOTIFY wasn't working for Windows systems to reload the schema (it's fixed but not yet released in Docker). Does stopping and restarting the PostgREST Docker container help in any way?

[MonkOfTheFeels]

Thanks @laurenceisla for the help, restarting my container didnt help but I tried rebuilding my entire docker compose and that seemed to work. Must have been something I was doing on my side that caused it to not reload the schema.