Supabase: Resource Embedding on Computed relationship

Question

I am using supabase (postgREST under the hood).
I have 2 tables:
challenge(name: string, date_begin: date, date_end: date)
timelog(created_at: timestamp, time_reached: bigint).

Those are not linked with a foreign key due to technical limitations.

Now I want to get a history of all timelogs that are within the date_begin and date_end of a challenge ... but I am only interested in the highest time_reached number of a day.

CREATE OR REPLACE FUNCTION public.history_timelogs(challenge)
 RETURNS TABLE(date date, time_reached bigint)
 LANGUAGE sql
 STABLE
AS $$
    SELECT
        DATE(created_at),
        max(timelog.time_reached) AS time_reached
    FROM
        timelog
    WHERE
        timelog.created_at > $1.date_begin AND timelog.created_at <= $1.date_end
    GROUP BY
        DATE(created_at);
$$

Within POSTGRES running the CMD:

SELECT
    challenge."name",
    history_timelogs (challenge)
FROM
    challenge

Seems to work:

Challenge 1; (2023-05-08,100)
Challenge 1; (2023-05-09,80)
Challenge 2; (2023-05-08,100)
Challenge 2; (2023-05-09,80)

But running it from Supabase with GET /rest/v1/challenge?select=name,history_timelogs(challenge) give me an error I can't resolve.

{
    "code": "PGRST200",
    "details": "Searched for a foreign key relationship between 'challenge' and 'history_timelogs' in the schema 'public', but no matches were found.",
    "hint": "Perhaps you meant 'timelog' instead of 'history_timelogs'.",
    "message": "Could not find a relationship between 'challenge' and 'history_timelogs' in the schema cache"
}

What I want to achive is something like that:

[
  {
   "name": "Challenge 1",
   "history_timelogs: [
      {"date": "2023-05-08", "time_reached": 100},
      {"date": "2023-05-09", "time_reached": 80}
   ]
  },
  {
   "name": "Challenge 2",
   "history_timelogs: [
      {"date": "2023-05-08", "time_reached": 100},
      {"date": "2023-05-09", "time_reached": 80}
   ]
  }
]

Thanks for any ideas or pointers!

Answer 1

Try changing the function signature to include timelog:

CREATE OR REPLACE FUNCTION public.history_timelogs(challenge)
RETURNS SETOF timelog
LANGUAGE sql
STABLE
AS $$
    SELECT
        timelog.col1,
        timelog.col2,
        -- ..
        max(timelog.time_reached) AS time_reached
    FROM
        timelog
    WHERE
        timelog.created_at > $1.date_begin AND timelog.created_at <= $1.date_end
    GROUP BY
        DATE(created_at);
$$

As documented on computed relationships, the table type has to be in the RETURNS.