S
Supabase•8h ago
jins

Postgis Error: function st_makepoint(double precision, double precision) does not exist

I am using postgis in a project and I have a Postgres function that takes the Longitude/Latitude when data is inserted into a row and calculates the location geometry field. I am trying to setup a new database and seed it but seeding the table with geometry postgis field is failing. This works on the original DB so I'm not sure what's happening. postgis extension is enabled. the error says: 
cause: PostgresError: function st_makepoint(double precision, double precision) does not exist

hint: 'No function matches the given name and argument types. You might need to add explicit type casts.',
where: 'PL/pgSQL function public.set_location_from_coords() line 4 at assignment',
internal_position: '28',
internal_query: 'NEW.location := ST_SetSRID(ST_MakePoint(NEW.longitude, NEW.latitude), 4326)',
cause: PostgresError: function st_makepoint(double precision, double precision) does not exist

hint: 'No function matches the given name and argument types. You might need to add explicit type casts.',
where: 'PL/pgSQL function public.set_location_from_coords() line 4 at assignment',
internal_position: '28',
internal_query: 'NEW.location := ST_SetSRID(ST_MakePoint(NEW.longitude, NEW.latitude), 4326)',
My function and trigger: 
CREATE OR REPLACE FUNCTION public.set_location_from_coords()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
  IF NEW.longitude IS NOT NULL AND NEW.latitude IS NOT NULL THEN
    NEW.location := public.ST_SetSRID(public.ST_MakePoint(NEW.longitude, NEW.latitude), 4326);
  ELSE
    RAISE EXCEPTION 'Longitude and latitude must not be null';
  END IF;
  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_set_location
BEFORE INSERT OR UPDATE ON rinks
FOR EACH ROW
EXECUTE FUNCTION set_location_from_coords();
CREATE OR REPLACE FUNCTION public.set_location_from_coords()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
  IF NEW.longitude IS NOT NULL AND NEW.latitude IS NOT NULL THEN
    NEW.location := public.ST_SetSRID(public.ST_MakePoint(NEW.longitude, NEW.latitude), 4326);
  ELSE
    RAISE EXCEPTION 'Longitude and latitude must not be null';
  END IF;
  RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trg_set_location
BEFORE INSERT OR UPDATE ON rinks
FOR EACH ROW
EXECUTE FUNCTION set_location_from_coords();
4 Replies
garyaustin
garyaustin•8h ago
Normally you would not install postgis into the public schema. You may have done that on your other project though... Default is to install into extensions.
jins
jinsOP•8h ago
so would i change public.ST_SetSRID()?
garyaustin
garyaustin•8h ago
Try without public or changed to extensions. Go the the Database tab and Extensions and see where you installed it.
jins
jinsOP•7h ago
Thanks this works: 
CREATE OR REPLACE FUNCTION public.set_location_from_coords()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = 'public, pg_temp'
AS $$
BEGIN
  IF NEW.longitude IS NOT NULL AND NEW.latitude IS NOT NULL THEN
    NEW.location := extensions.ST_SetSRID(extensions.ST_MakePoint(NEW.longitude, NEW.latitude), 4326);
  ELSE
    RAISE EXCEPTION 'Longitude and latitude must not be null';
  END IF;
  RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION public.set_location_from_coords()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = 'public, pg_temp'
AS $$
BEGIN
  IF NEW.longitude IS NOT NULL AND NEW.latitude IS NOT NULL THEN
    NEW.location := extensions.ST_SetSRID(extensions.ST_MakePoint(NEW.longitude, NEW.latitude), 4326);
  ELSE
    RAISE EXCEPTION 'Longitude and latitude must not be null';
  END IF;
  RETURN NEW;
END;
$$;
Thanks gary

Did you find this page helpful?