Function not found using rpc

[donocode]

Bug report

Describe the bug

I have created a function called aggregated_track_analytics with two parameters, it returns a table. When trying to get results from the function I get an error from the supabase client that the function could not be found.

TS Call

const { data } = await this._client
      .rpc<DBAggregatedTrackAnalytics>(
        DB_FUNCTIONS.aggregated_track_analytics,
        {
          _activity_type: filters.activityType ?? null,
          _user_id: filters.userId ?? null,
        }
      )
      .select();

Error

{
  message: 'Could not find the public.aggregated_track_analytics(_activity_type, _user_id) function or the public.aggregated_track_analytics function with a single unnamed json or jsonb parameter in the schema cache',
  hint: 'If a new function was created in the database with this name and parameters, try reloading the schema cache.'
}

It has been 12 hours since I created this function and I have tried executing NOTIFY pgrst, 'reload schema'. I extracted the function schema so maybe you are able to see something important there?

specific_name	ordinal_position	parameter_mode	parameter_name	data_type
aggregated_track_analytics_18428	1	IN	_activity_id	text
aggregated_track_analytics_18428	2	IN	_user_id	uuid
aggregated_track_analytics_18428	3	OUT	track_id	text
aggregated_track_analytics_18428	4	OUT	user_id	uuid
aggregated_track_analytics_18428	5	OUT	activity_type	text
aggregated_track_analytics_18428	6	OUT	play_count	integer
aggregated_track_analytics_18428	7	OUT	last_played_date	timestamp without time zone
aggregated_track_analytics_18428	8	OUT	distance	numeric
aggregated_track_analytics_18428	9	OUT	ascent	numeric
aggregated_track_analytics_18428	10	OUT	descent	numeric
aggregated_track_analytics_18428	11	OUT	max_elevation	numeric
aggregated_track_analytics_18428	12	OUT	avg_velocity	numeric
aggregated_track_analytics_18428	13	OUT	max_velocity	numeric
aggregated_track_analytics_18428	14	OUT	avg_heartrate	numeric
aggregated_track_analytics_18428	15	OUT	avg_power	numeric
aggregated_track_analytics_18428	16	OUT	max_power	numeric
aggregated_track_analytics_18428	17	OUT	moving_duration	numeric

To Reproduce

I don't think any steps I write here will actually be useful. I saw another closed issue https://github.com/supabase/supabase/issues/7327 so I presume it isn't an isolated issue.

Expected behavior

The function is found and can be used in a query via rpc as the docs suggest https://supabase.com/docs/guides/database/functions

Screenshots

N/A

System information

• OS: Supabase runs in server on Ubuntu (WSL2)
• Browser: Edge
• Version of supabase-js: 1.35.6 (first 1.35.4 but upgraded to validate)
• Version of Node.js: 16.13.1

Additional context

Add any other context about the problem here.

[GaryAustin1]

You are calling it with a parameter name _activity_type, your dump shows the input name _activity_id. They must match. Is the function in the public schema?

[donocode]

Well this is an embarrassing case of parameter blindness. Thanks for noticing @GaryAustin1

A DX upgrade could be to list partially matching functions if there is no exact match, but I'll take that to PostgREST .

[AndreiSaliba]

Hi, I'm getting the same error from all functions with parameters, and the parameters on JS are the same as the SQL function. I have no idea how to fix it.

SQL

CREATE OR REPLACE FUNCTION getUserProfile(p_username varchar(15), p_userRequestingProfile varchar(15))
RETURNS json
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN (SELECT json_build_object(
          'profile', ((SELECT row_to_json(p.*) FROM public.profile p WHERE p.username = p_username)),
          'isFollowedByRequest', EXISTS(SELECT *
                                        FROM public.follows f
                                        WHERE f.followed = (SELECT userid
                                                            FROM public.profile p
                                                            WHERE p.username = p_username)
                                        AND f.follower = (SELECT userid
                                                            FROM public.profile p
                                                            WHERE p.username = p_userRequestingProfile))
  ));
END;
$$;

JS

export const getUserProfile = async (
    username: string,
    userRequestingProfile: string
) => {
    const { data, error } = await supabase.rpc("getuserprofile", {
        p_username: "Test1",
        p_userRequestingProfile: "Test2",
    });

    console.log(data, error);
};

Error

{
  "message": "Could not find the public.getuserprofile(p_userRequestingProfile, p_username) function or the public.getuserprofile function with a single unnamed json or jsonb parameter in the schema cache",
  "code": "PGRST202",
  "details": null,
  "hint": "If a new function was created in the database with this name and parameters, try reloading the schema cache."
}

[soedirgo]

p_userRequestingProfile

I think you need to either quote the name in the SQL part or lower-case it in the JS part.

[GaryAustin1]

@AndreiSaliba I'll be a bit stronger than soedirgo... Don't use capital letters in Postgres names for tables, columns or functions. You will regret it, as it will bite you in the future when you forget the quotes somewhere. There are many issues like yours, even from experienced SQL coders who miss it but don't see it before asking for help. The only exception is if you have no choice due to something like Prisma.

For instance, when you get the function working, you when then hit a problem in the last WHERE as you don't have a name that uses capital letters quoted. It will be turned to all lower case and not match. Edit: I guess technically it will match in your case as both it’s declaration and use will get converted to lowercase.

[AndreiSaliba]

Sorry to ask again but after updating 2 more procedures to remove uppercase letters I'm still getting the same error. I think the error is only happening with procedures, functions work fine. The procedures work when I call them through the sql editor or datagrip but don't work with rpc.

Procedure 1

CREATE OR REPLACE PROCEDURE public.update_userprofile(
    _userid uuid,
    _name varchar(50),
    _username varchar(15),
    _location varchar(30),
    _description varchar(160),
    _url varchar(100),
    _profile_image_url varchar,
    _banner_image_url varchar)
LANGUAGE plpgsql
AS
$$
BEGIN
    UPDATE public.profile p
    SET "name" = COALESCE(_name, p.name),
        "username" = COALESCE(_username, p.username),
        "location" = COALESCE(_location, p.location),
        "description" = COALESCE(_description, p.description),
        "url" = COALESCE(_url, p.url),
        "profile_image_url" = COALESCE(_profile_image_url, p.profile_image_url),
        "profile_banner_url" = COALESCE(_banner_image_url, p.profile_banner_url)
    WHERE p.userid = _userid;
    COMMIT;
END
$$;

Procedure 2

CREATE OR REPLACE procedure public.follow_user(_followed uuid, _follower uuid)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO public.follows(followed, follower)
    VALUES(_followed, _follower);

    UPDATE public.profile
    SET followers_count = followers_count + 1
    WHERE userid = _followed;

    UPDATE public.profile
    SET following_count = following_count + 1
    WHERE userid = _follower;
    COMMIT;
END
$$;

JS 1

export const updateUserProfile = async (
    userID: string,
    name: string,
    username: string,
    description: string,
    location: string,
    url: string,
    profileImageURL: string,
    bannerImageURL: string
) => {
    const { error } = await supabase.rpc("update_userprofile", {
        _userid: userID,
        _name: name,
        _username: username,
        _location: location,
        _description: description,
        _url: url,
        _profile_image_url: profileImageURL,
        _banner_image_url: bannerImageURL,
    });

    error && console.log(error);
};

JS 2

export const followUser = async (followedUser: string, follower: string) => {
    const { error } = await supabase.rpc("follow_user", {
        _followed: followedUser,
        _follower: follower,
    });

    error && console.log(error);
};

[GaryAustin1]

This kind of general debug stuff is better asked on Supabase Discord. Lots of users to help there.

Sent from my iPhone

On Sep 27, 2022, at 1:20 PM, Andrei Saliba @.***> wrote:

 Sorry to ask again but after updating 2 more procedures to remove uppercase letters I'm still getting the same error. I think the error is only happening with procedures, functions work fine. The procedures work when I call them through the sql editor or datagrip but don't work with rpc.

Procedure 1

CREATE OR REPLACE PROCEDURE public.update_userprofile( _userid uuid, _name varchar(50), _username varchar(15), _location varchar(30), _description varchar(160), _url varchar(100), _profile_image_url varchar, _banner_image_url varchar) LANGUAGE plpgsql AS $$ BEGIN UPDATE public.profile p SET "name" = COALESCE(_name, p.name), "username" = COALESCE(_username, p.username), "location" = COALESCE(_location, p.location), "description" = COALESCE(_description, p.description), "url" = COALESCE(_url, p.url), "profile_image_url" = COALESCE(_profile_image_url, p.profile_image_url), "profile_banner_url" = COALESCE(_banner_image_url, p.profile_banner_url) WHERE p.userid = _userid; COMMIT; END $$; Procedure 2

CREATE OR REPLACE procedure public.follow_user(_followed uuid, _follower uuid) LANGUAGE plpgsql AS $$ BEGIN INSERT INTO public.follows(followed, follower) VALUES(_followed, _follower);

UPDATE public.profile
SET followers_count = followers_count + 1
WHERE userid = _followed;

UPDATE public.profile
SET following_count = following_count + 1
WHERE userid = _follower;
COMMIT;

END $$; JS 1

export const updateUserProfile = async ( userID: string, name: string, username: string, description: string, location: string, url: string, profileImageURL: string, bannerImageURL: string ) => { const { error } = await supabase.rpc("update_userprofile", { _userid: userID, _name: name, _username: username, _location: location, _description: description, _url: url, _profile_image_url: profileImageURL, _banner_image_url: bannerImageURL, });

error && console.log(error);

}; JS 2

export const followUser = async (followedUser: string, follower: string) => { const { error } = await supabase.rpc("follow_user", { _followed: followedUser, _follower: follower, });

error && console.log(error);

}; — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.

[AndreiSaliba]

Ok thanks.

[soedirgo]

Actually for this specific case, it's because we only support functions, not procedures.