|
1 | 1 | CREATE OR REPLACE FUNCTION da.chronologybyid(_chronid integer) |
2 | | - 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) |
| 2 | + RETURNS TABLE(controls json, |
| 3 | + "Default" boolean, |
| 4 | + ChronologyName character varying, |
| 5 | + AgeType character varying, |
| 6 | + AgeModel character varying, |
| 7 | + AgeOlder integer, |
| 8 | + ChronologyID integer, |
| 9 | + AgeYounger integer, |
| 10 | + datasets json, |
| 11 | + notes character varying, |
| 12 | + dateprepared date) |
3 | 13 | LANGUAGE sql |
4 | 14 | AS $function$ |
5 | 15 |
|
6 | | -SELECT ndb.chronologies.chronologyid, ndb.agetypes.agetype, ndb.chronologies.isdefault, ndb.chronologies.chronologyname, ndb.chronologies.dateprepared, |
7 | | -ndb.chronologies.agemodel, ndb.chronologies.ageboundyounger, ndb.chronologies.ageboundolder,ndb.chronologies.notes, ndb.chroncontrols.chroncontrolid, |
8 | | -ndb.chroncontrols.depth AS controldepth, ndb.chroncontrols.thickness AS controlthickness, ndb.chroncontrols.age AS controlage, ndb.chroncontrols.agelimityounger AS controlageyounger, |
9 | | -ndb.chroncontrols.agelimitolder AS controlageolder, ndb.chroncontroltypes.chroncontroltype |
10 | | -FROM ndb.chronologies INNER JOIN ndb.agetypes ON ndb.chronologies.agetypeid = ndb.agetypes.agetypeid LEFT JOIN |
11 | | -ndb.chroncontrols ON ndb.chronologies.chronologyid = ndb.chroncontrols.chronologyid LEFT JOIN |
12 | | -ndb.chroncontroltypes ON ndb.chroncontrols.chroncontroltypeid = ndb.chroncontroltypes.chroncontroltypeid |
13 | | -WHERE ndb.chronologies.chronologyid = _chronid; |
14 | | - |
| 16 | + SELECT json_agg(json_build_object('AgeYoungest', ccr.agelimityounger, |
| 17 | + 'Age', ccr.age, |
| 18 | + 'ControlType', ccrt.chroncontroltype, |
| 19 | + 'ChronControlID', ccr.chroncontrolid, |
| 20 | + 'Depth', ccr.depth, |
| 21 | + 'AgeOldest', ccr.agelimitolder, |
| 22 | + 'Thickness', ccr.thickness)) AS controls, |
| 23 | + chr.isdefault AS Default, |
| 24 | + chr.chronologyname AS ChronologyName, |
| 25 | + aty.agetype AS AgeType, |
| 26 | + chr.agemodel AS AgeModel, |
| 27 | + chr.ageboundolder AS AgeOlder, |
| 28 | + chr.chronologyid AS ChronologyID, |
| 29 | + chr.ageboundyounger AS AgeYounger, |
| 30 | + json_agg(json_build_object('DatasetType', dst.datasettype, |
| 31 | + 'DatasetID', ds.datasetid)) AS datasets, |
| 32 | + chr.notes AS Notes, |
| 33 | + chr.dateprepared AS DatePrepared |
| 34 | + FROM ndb.chronologies AS chr |
| 35 | + INNER JOIN ndb.agetypes AS aty ON chr.agetypeid = aty.agetypeid |
| 36 | + LEFT JOIN ndb.chroncontrols AS ccr ON chr.chronologyid = ccr.chronologyid |
| 37 | + LEFT JOIN ndb.chroncontroltypes AS ccrt ON ccr.chroncontroltypeid = ccrt.chroncontroltypeid |
| 38 | + LEFT JOIN ndb.datasets AS ds ON ds.collectionunitid = chr.collectionunitid |
| 39 | + LEFT JOIN ndb.datasettypes AS dst ON dst.datasettypeid = ds.datasettypeid |
| 40 | + WHERE chr.chronologyid = 123 |
| 41 | + GROUP BY chr.isdefault, |
| 42 | + chr.chronologyname, |
| 43 | + aty.agetype, |
| 44 | + chr.ageboundolder, |
| 45 | + chr.ageboundyounger, |
| 46 | + chr.agemodel, |
| 47 | + chr.notes, |
| 48 | + chr.chronologyid, |
| 49 | + chr.dateprepared |
15 | 50 | $function$ |
0 commit comments