Postgrest is unable to determine relationship between user-defined type and table using computed relationship function #3096
Description
Environment
- PostgreSQL version: 15.1
- PostgREST version: 11.2.0
- Operating system: Ubuntu - supabase image
Description of issue
Postgrest is unable to determine relationship between user-defined type and table using computed relationship function.
The example below is somewhat contrived (and could be accomplished with FTS) but we are using a similar pattern for more complex search queries (e.g. vector-based search).
We would also like to do further nested embedding on the related table.
CREATE TABLE public.source (
"parent_id" UUID REFERENCES public.source ("id") ON DELETE CASCADE,
"id" UUID NOT NULL PRIMARY KEY,
"content" TEXT NOT NULL
);
INSERT INTO public.source (parent_id, id, content)
VALUES
(NULL, '1ef8ddf5-aecf-4d0a-86c8-8e73cde57eba', 'test'),
(NULL, '49529109-96a4-44bf-bd90-602e9ba978fe', 'test'),
('49529109-96a4-44bf-bd90-602e9ba978fe', '5d835af8-f3d2-419b-96a1-643f9ec366a2', 'test');
CREATE TYPE public.search_result AS (
id UUID,
similarity FLOAT
);
CREATE FUNCTION public.source_from_result (
public.search_result
) RETURNS SETOF public.source ROWS 1
STABLE LANGUAGE SQL AS $$
SELECT * FROM public.source WHERE id = $1.id
$$;
CREATE FUNCTION public.search_source (
query_text TEXT
) RETURNS SETOF public.search_result
LANGUAGE plpgsql AS $$
BEGIN
RETURN query
SELECT
source.id,
ts_rank(
to_tsvector(source.content),
plainto_tsquery(query_text)
)::float * 10 AS similarity
FROM public.source
ORDER BY similarity DESC;
END;
$$;This call is successful:
POST http://localhost:54321/rest/v1/rpc/search_source?select=id,similarity
{"query_text": "test"}
[
{
"id": "9dcf0d49-9498-4155-9246-4eafe8f11a0b",
"similarity": 0.607927106320858
},
{
"id": "905c225b-0661-41f1-9940-6623022e315b",
"similarity": 0.607927106320858
},
{
"id": "56b27c58-2ab6-4664-82e8-bec8fbbccace",
"similarity": 0
}
]This call fails:
POST http://localhost:54321/rest/v1/rpc/search_source?select=id,similarity,source_from_result(id,content)
{"query_text": "test"}
{
"code": "PGRST200",
"details": "Searched for a foreign key relationship between 'search_result' and 'source_from_result' in the schema 'public', but no matches were found.",
"hint": null,
"message": "Could not find a relationship between 'search_result' and 'source_from_result' in the schema cache"
}Edit: added Postgresql and Postrest version