Error function st_geogfromtext(text) does not exist occurs with postgis and rpc

[dshukertjr]

I am creating a geo based video sharing app. To achieve this, I have enabled postgis on my project, and I have the following table with location being the geo point.

create table if not exists public.videos (
    id uuid not null primary key DEFAULT uuid_generate_v4 (),
    user_id uuid references public.users not null,
    created_at timestamp with time zone default timezone('utc' :: text, now()) not null,
    url text,
    image_url text,
    thumbnail_url text,
    gif_url text,
    description varchar(320),
    location geography(POINT)
);

I also defined function to retrieve videos from the above table like this:

create or replace function nearby_videos(location text)
returns table(id uuid, image_url text, thumbnail_url text, gif_url text, location geography(POINT))
as 
$func$
    select id, image_url, thumbnail_url, gif_url, location
    from videos
    order by videos.location <-> ST_GeogFromText($1)
    limit 20;
$func$
language sql;

Now when I execute the following query in my SQL editor inside my project's console, I am successfully able to get the video data without any errors.

select * from nearby_videos(location := 'POINT(37.611100 55.756926)');

But when I call the function from my Flutter app, like this, I get the following error:

final res = await supabaseClient.rpc('nearby_videos', params: {'location': 'POINT(37.611100 55.756926)'}).execute();

code:"42883"
message: "function st_geogfromtext(text) does not exist"
hint: "No function matches the given name and argument types. You might need to add explicit type casts."
details: null

Does anyone know what I could be missing here?

[steve-chavez]

Check this issue supabase/postgrest-js#168.

New projects won't have this error.

You can work around it by using extensions.ST_GeogFromText.

[dshukertjr]

Thanks Steve for such a quick response! You're the best!

After reading through the issue, running these SQL commands solved my issue:

grant usage on schema extensions to anon;
grant usage on schema extensions to authenticated;

One thing though, since you mentioned that this issue would not happen in a new project, I went ahead and spun up a brand new project just now, and still ran into the permission error described here. Is this expected?

Either way, thank you so much for your support!

[steve-chavez]

Oh, I meant that "function st_geogfromtext(text) does not exist" type of errors won't happen on new projects. Those errors where the rpc call is not able to find the extension.

and still ran into the permission error described here. Is this expected?

For now yes, you have to grant those permissions.