Supabase RPC function not found

Question

I have written a psql function in order to not have logic in my mobile app's code.

CREATE OR REPLACE FUNCTION getRepeatedQuestionsByUserIdWithSubjectNameAndOptions(userId uuid)
RETURNS TABLE (
   question_id uuid,
   question_content text,
   question_explanatory_text text,
   question_created_at timestamp with time zone,
   quiz_id uuid,
   subject_name text,
   options jsonb -- Include options as a JSONB array
)
AS $$
BEGIN
   RETURN QUERY
   SELECT
       q.id AS question_id,
       q.question_content,
       q.question_explanatory_text,
       q.question_created_at,
       q.quiz_id,
       s.subject_name,
       (
           SELECT jsonb_agg(
               jsonb_build_object(
                   'id', o.id,
                   'option_content', o.option_content,
                   'option_correct', o.option_correct
               )
           )
           FROM public.options o
           WHERE o.question_id = q.id
       ) AS options
   FROM
       public.questions_to_repeat r
   INNER JOIN
       public.questions q ON r.question_id = q.id
   INNER JOIN
       public.quizzes qu ON q.quiz_id = qu.id
   INNER JOIN
       public.subcategories sc ON qu.subcategory_id = sc.id
   INNER JOIN
       public.categories c ON sc.category_id = c.id
   INNER JOIN
       public.subjects s ON c.subject_id = s.id
   WHERE
       r.user_id = userId;
END;
$$
LANGUAGE plpgsql;

the problem is that when I try to run this code:

String userId = this._supabaseClient.auth.currentUser!.id;
    return await this._supabaseClient.rpc('getRepeatedQuestionsByUserIdWithSubjectNameAndOptions', params: {'userId': userId});

I get this error: Unhandled Exception: PostgrestException(message: Could not find the function public.getRepeatedQuestionsByUserIdWithSubjectNameAndOptions(userId) in the schema cache, code: PGRST202, details: Searched for the function public.getRepeatedQuestionsByUserIdWithSubjectNameAndOptions with parameter userId or with a single unnamed json/jsonb parameter, but no matches were found in the schema cache., hint: Perhaps you meant to call the function public.getrepeatedquestionsbyuseridwithsubjectnameandoptions)

I have tried running the function directly in the supabase SQL editor:

SELECT * FROM getRepeatedQuestionsByUserIdWithSubjectNameAndOptions('user_id');

and everything works fine, any tips? Thanks

Supabase RPC function not found

0 Answers0