postgres-error: function st_intersects(geography, geography, integer) does not exist

Question

I have a table business_locations with a postGIS location column and I try to fetch the rows that are within a bounding box (function arguments).

I am trying to run this function in Hasura:

CREATE OR REPLACE FUNCTION search_businesses_near_project(lngW integer, latS integer, lngE integer, latN integer)
RETURNS SETOF business_locations AS $$
  SELECT  A.location, A.route, A.locality, A.administrative_area_level_1, A.administrative_area_level_2, A.country, A.business_id
  FROM business_locations A where ST_Intersects(A.location::geography, ST_MakeEnvelope(lngW, latS, lngE, latN, 4326)::geography, 4326)
$$ LANGUAGE sql STABLE;

The error returned:

{
    "internal": {
        "statement": "CREATE OR REPLACE FUNCTION search_businesses_near_project(project_location geography, lngW integer, latS integer, lngE integer, latN integer)\r\nRETURNS SETOF business_locations AS $$\r\n  SELECT  A.location, A.route, A.locality, A.administrative_area_level_1, A.administrative_area_level_2, A.country, A.business_id\r\n  FROM business_locations A where ST_Intersects(project_location, ST_MakeEnvelope(lngW, latS, lngE, latN, 4326)::geography, 4326)\r\n$$ LANGUAGE sql STABLE;",
        "prepared": false,
        "error": {
            "exec_status": "FatalError",
            "hint": "No function matches the given name and argument types. You might need to add explicit type casts.",
            "message": "function st_intersects(geography, geography, integer) does not exist",
            "status_code": "42883",
            "description": null
        },
        "arguments": []
    },
    "path": "$.args[0].args",
    "error": "query execution failed",
    "code": "postgres-error"
}

Laurenz Albe · Accepted Answer · 2020-06-12T13:38:43.280

1

st_intersects takes only two arguments. Omit the superfluous SRID:

CREATE OR REPLACE FUNCTION search_businesses_near_project(
   lngW integer,
   latS integer,
   lngE integer,
   latN integer
) RETURNS SETOF business_locations AS $$
   SELECT *
   FROM business_locations A
   WHERE st_intersects(
            A.location::geography,
            ST_MakeEnvelope(lngW, latS, lngE, latN, 4326)::geography
   )
$$ LANGUAGE sql STABLE;

edited Jun 12 '20 at 13:38

answered Jun 12 '20 at 11:03

Laurenz Albe

209,280
17
206
263

Thanks it works but now I am getting this error: ` "message": "return type mismatch in function declared to return business_locations", "status_code": "42P13", "description": "Final statement returns too few columns."`. Is it not possible to return a few columns only? I have too return all of them? – Constantinos N Jun 12 '20 at 11:18
Cast the result appropriately: `SELECT ROW(a.location, a.route, ...)::business_locations` – Laurenz Albe Jun 12 '20 at 11:25
It doesnt work. I get `Cannot cast type geography to uuid in column 1`. What I used is `SELECT ROW(a.location, a.business_id)::business_locations`. The first column in the `business_locations` table is of type `uuid` and its called `id`. – Constantinos N Jun 12 '20 at 11:53
Then you should `RETURN business_locations FROM business_locations WHERE ...`. – Laurenz Albe Jun 12 '20 at 12:00
In Hasura you can only return `SETOF`, so maybe its not possibe. I cant tell exactly because I am new with postgres. https://hasura.io/docs/1.0/graphql/manual/schema/custom-functions.html – Constantinos N Jun 12 '20 at 12:23
I have added the complete function to the answer. – Laurenz Albe Jun 12 '20 at 12:32
Actually thats wrong. It should be `SELECT business_locations.*` or it doesnt work. I also returned a view instead in order to get the exact columns I need. This way I get columns from other tables also. – Constantinos N Jun 12 '20 at 12:57

postgres-error: function st_intersects(geography, geography, integer) does not exist

1 Answers1