Joining relationships from `.rpc` function calls throws error

[davidfant]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I'm getting errors when joining relationships from rpc function calls, but the supabase-js SDK infers the correct types (suggesting it is possible)

To Reproduce

I have a Postgres function

CREATE OR REPLACE FUNCTION public."getTokenLineage"(...)
 RETURNS TABLE("triggerId" uuid, ...)
 LANGUAGE plpgsql
AS $function$
BEGIN
	...

This performs a complex filter and returns a list of triggerIds, which map to the triggers table. To fetch this I'm doing:

const triggers = await supabase.rpc("getTokenLineage", { ... });

When I write the following, supabase-js type inference correctly shows me the right types

const triggers = await supabase
    .rpc("getTokenLineage", { ... })
    .select(
      `
        triggerId,
        trigger(
          id,
          status
        )
    `
    )

However that query doesn't work and gives the following error:

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

Expected behavior

The trigger table is joined and in the select statement I can join arbitrary fields and relationships

System information

• OS: macOS
• Browser (if applies) [e.g. chrome, safari]
• Version of supabase-js: @supabase/supabase-js@^2.43.4
• Version of Node.js: v21.1.0

[avallete]

Hey,

The runtime error is expected I think AFAIK postgrest need a function that return a setof to know it map to another table:

https://docs.postgrest.org/en/v12/references/api/resource_embedding.html#foreign-key-joins-on-table-valued-functions

However, I think the types will still have troubles with this because some more work is required to support rpc call embedding typing (see: https://github.com/supabase/postgrest-js/compare/d52234f5c74318351be999d1a0d6273353335653..c6c279564321febd910de52fb326b3b8f5e7d635#diff-58acaadb1530eeb222dd2a23a57bbe8e426aca227f2f1843d29fe2fe9f5e1ee2L75-L134)

So you should be able to achieve what you want by making your function return a SETOF, however, you will need to manually override the select result with returns for now.