yerffejytnac/ISSUE.md

## ISSUE.md

      
    Raw
  

              ISSUE.md
            
          
    Hasura Postgres Custom Function Issue

I am experimenting with Hasura for the first time and wanted to toy around with a location-based app where a user could see a grid of locations from their current location.
Naturally, I referenced the blog posts https://blog.hasura.io/graphql-and-geo-location-on-postgres-using-hasura-562e7bd47a2f/ and https://blog.hasura.io/native-support-for-postgis-topology-operators-now-in-graphql-engine/ to get me started, and have kept things relatively the same as what the blog post outlined.
Original Function

-- SETOF table
CREATE TABLE user_venues (
user_id INTEGER,
location GEOGRAPHY(Point),
nearby_venues JSON
);

-- function returns a list of venues near a user based on the
-- input arguments distance_kms and userid
CREATE FUNCTION search_venues_near_user(userid integer, distance_kms integer)
RETURNS SETOF user_venues AS $$
SELECT  A.user_id, A.location,
(SELECT json_agg(row_to_json(B)) FROM venue B
 WHERE (
   ST_Distance(
     ST_Transform(B.location::Geometry, 3857),
     ST_Transform(A.location::Geometry, 3857)
   ) /1000) < distance_kms
 ) AS nearby_venues
FROM user_location A where A.user_id = userid
$$ LANGUAGE sql STABLE;
Where it differs, is in the custom function — I am returning GeoJSON as mentioned in the comments of the original blog post, but also wanted to extend it further by calculating the distance relative to the current user and returning that in the response data as well.
Modified Function

CREATE
OR REPLACE FUNCTION public.search_venues_near_user(userid integer, distance_kms integer) RETURNS SETOF user_venues LANGUAGE sql STABLE AS $ function $
SELECT
  A.user_id,
  A.location,
  (
    SELECT
      json_agg(
        json_build_object(
          'name',
          B.name,
          'type',
          B.type,
          'geometry',
          ST_AsGeoJSON(B.location) :: json,
          'distance',
          ST_Distance(
            ST_Transform(B.location :: Geometry, 3857),
            ST_Transform(A.location :: Geometry, 3857)
          ) / 1000
        )
      )
    FROM
      venue B
    WHERE
      (
        ST_Distance(
          ST_Transform(B.location :: Geometry, 3857),
          ST_Transform(A.location :: Geometry, 3857)
        ) / 1000
      ) < distance_kms
  ) AS nearby_venues
FROM
  user_location A
where
  A.user_id = userid $ function $
The issue I am running into now is trying to use ORDER BY correctly so that I could return the results in ASC order, but no matter where I try to place the ORDER BY, I get errors when trying to save the function.
Any ideas/recommendations on how to solve that problem as well as DRY-ing up the function code would be greatly appreciated!