Added the DROP FUNCTION & converted to JSON object.

SimonGoring · SimonGoring · commit 76ec35e2b6b6 · 2021-07-22T14:55:52.000-07:00
diff --git a/function/ap/siteobject.sql b/function/ap/siteobject.sql
@@ -1,22 +1,17 @@
+
+DROP FUNCTION ap.siteobject(_siteid integer);
 CREATE OR REPLACE FUNCTION ap.siteobject(_siteid integer)
- RETURNS TABLE(siteid integer, 
-               sitename character varying,
-               sitedescription character varying,
-               geography text,
-               area double precision,
-               altitude double precision,
-               notes text,
-               geopolitical character varying[])
+ RETURNS TABLE(siteid integer, site jsonb)
  LANGUAGE sql
 AS $function$
     SELECT sts.siteid,
-        sts.sitename as sitename,
-        sts.sitedescription AS sitedescription,
-        ST_AsGeoJSON(sts.geog,5,2) as geography,
-        sts.area AS area,
-        sts.altitude AS altitude,
-        sts.notes AS notes,
-        gpn.names AS geopolitical
+        jsonb_build_object('sitename', sts.sitename,
+                    'sitedescription', sts.sitedescription,
+                          'geography', ST_AsGeoJSON(sts.geog,5,2),
+                               'area', sts.area,
+                           'altitude', sts.altitude,
+                              'notes', sts.notes,
+                       'geopolitical', gpn.names) AS site
     FROM
     ndb.sites AS sts
     LEFT JOIN ap.geopolnames AS gpn ON gpn.siteid = sts.siteid
@@ -25,29 +20,26 @@ AS $function$
 		 sts.area, sts.altitude, sts.notes, gpn.names;
 $function$;
 
+COMMENT ON FUNCTION ap.siteobject(_siteid integer) IS 'Create the JSON object used to return relevant site metadata from a single siteid.';
+
+DROP FUNCTION ap.siteobject(_siteid integer[]);
 CREATE OR REPLACE FUNCTION ap.siteobject(_siteid integer[])
- RETURNS TABLE(siteid integer, 
-               sitename character varying,
-               sitedescription character varying,
-               geography text,
-               area double precision,
-               altitude double precision,
-               notes text,
-               geopolitical character varying[])
+ RETURNS TABLE(siteid integer, site jsonb)
  LANGUAGE sql
 AS $function$
     SELECT sts.siteid,
-        sts.sitename as sitename,
-        sts.sitedescription AS sitedescription,
-        ST_AsGeoJSON(sts.geog,5,2) as geography,
-        sts.area AS area,
-        sts.altitude AS altitude,
-        sts.notes AS notes,
-        gpn.names AS geopolitical
+        jsonb_build_object('sitename', sts.sitename,
+                    'sitedescription', sts.sitedescription,
+                          'geography', ST_AsGeoJSON(sts.geog,5,2),
+                               'area', sts.area,
+                           'altitude', sts.altitude,
+                              'notes', sts.notes,
+                       'geopolitical', gpn.names) AS site
     FROM
     ndb.sites AS sts
     LEFT JOIN ap.geopolnames AS gpn ON gpn.siteid = sts.siteid
     WHERE sts.siteid = ANY(_siteid)
     GROUP BY sts.siteid, sts.sitename, sts.sitedescription, ST_AsGeoJSON(sts.geog,5,2), 
 		 sts.area, sts.altitude, sts.notes, gpn.names;
-$function$;
+$function$;
+COMMENT ON FUNCTION ap.siteobject(_siteid integer[]) IS 'Create the JSON object used to return relevant site metadata from an array of siteids.';
