Reformatted da/chronbyidv2 and fixed the smallint issue.

SimonGoring · SimonGoring · commit 182c10288e67 · 2019-04-03T11:45:26.000-07:00
diff --git a/function/da/chronologybyidv2.sql b/function/da/chronologybyidv2.sql
@@ -1,15 +1,43 @@
 CREATE OR REPLACE FUNCTION da.chronologybyidv2(chronid integer)
- RETURNS TABLE(chronologyid integer, agetype character varying, isdefault smallint, chronologyname character varying, dateprepared date, agemodel character varying, ageboundyounger integer, ageboundolder integer, datasetidnotes character varying, chroncontrolid integer, controldepth double precision, controlthickness double precision, controlage double precision, controlageyounger double precision, controlageolder double precision, chroncontroltype character varying)
+ RETURNS TABLE(chronologyid integer,
+               agetype character varying,
+               isdefault boolean,
+               chronologyname character varying,
+               dateprepared date,
+               agemodel character varying,
+               ageboundyounger integer,
+               ageboundolder integer,
+               datasetidnotes character varying,
+               chroncontrolid integer,
+               controldepth double precision,
+               controlthickness double precision,
+               controlage double precision,
+               controlageyounger double precision,
+               controlageolder double precision,
+               chroncontroltype character varying)
  LANGUAGE sql
 AS $function$
 
-SELECT ndb.chronologies.chronologyid, ndb.agetypes.agetype, ndb.chronologies.isdefault, ndb.chronologies.chronologyname, ndb.chronologies.dateprepared,
-ndb.chronologies.agemodel, ndb.chronologies.ageboundyounger, ndb.chronologies.ageboundolder,ndb.chronologies.notes, ndb.chroncontrols.chroncontrolid,
-ndb.chroncontrols.depth AS controldepth, ndb.chroncontrols.thickness AS controlthickness, ndb.chroncontrols.age AS controlage, ndb.chroncontrols.agelimityounger AS controlageyounger,
-ndb.chroncontrols.agelimitolder AS controlageolder, ndb.chroncontroltypes.chroncontroltype
-FROM ndb.chronologies INNER JOIN ndb.agetypes ON ndb.chronologies.agetypeid = ndb.agetypes.agetypeid LEFT JOIN
-ndb.chroncontrols ON ndb.chronologies.chronologyid = ndb.chroncontrols.chronologyid LEFT JOIN
-ndb.chroncontroltypes ON ndb.chroncontrols.chroncontroltypeid = ndb.chroncontroltypes.chroncontroltypeid
-WHERE ndb.chronologies.chronologyid =  chronid;
+SELECT chr.chronologyid,
+       aty.agetype,
+       chr.isdefault,
+       chr.chronologyname,
+       chr.dateprepared,
+       chr.agemodel,
+       chr.ageboundyounger,
+       chr.ageboundolder,
+       chr.notes,
+       chcr.chroncontrolid,
+       chcr.depth           AS controldepth,
+       chcr.thickness       AS controlthickness,
+       chcr.age             AS controlage,
+       chcr.agelimityounger AS controlageyounger,
+       chcr.agelimitolder   AS controlageolder,
+       cct.chroncontroltype
+FROM       ndb.chronologies AS chr
+  INNER JOIN ndb.agetypes         AS aty  ON           chr.agetypeid = aty.agetypeid
+  LEFT JOIN ndb.chroncontrols     AS chcr ON        chr.chronologyid = chcr.chronologyid
+  LEFT JOIN ndb.chroncontroltypes AS cct  ON chcr.chroncontroltypeid = cct.chroncontroltypeid
+WHERE chr.chronologyid =  chronid;
 
 $function$
