-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy path01_datasetinfo.sql
More file actions
76 lines (71 loc) · 4.43 KB
/
01_datasetinfo.sql
File metadata and controls
76 lines (71 loc) · 4.43 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
CREATE OR REPLACE FUNCTION doi.datasetinfo(dsid INT[])
RETURNS TABLE(datasetid integer, dataset json)
LANGUAGE sql
AS $function$
SELECT dts.datasetid,
json_build_object('site', json_build_object('siteid', sts.siteid,
'sitename', sts.sitename,
'sitedescription', sts.sitedescription,
'sitenotes', sts.notes,
'geography', ST_AsGeoJSON(sts.geog,5,2),
'altitude', sts.altitude,
'collectionunitid', clu.collectionunitid,
'collectionunit', clu.collunitname,
'handle', clu.handle,
'unittype', cts.colltype),
'dataset', json_build_object( 'datasetid', dts.datasetid,
'datasettype', dst.datasettype,
'datasetnotes', dts.notes,
'database', cstdb.databasename,
'doi', doi.dois,
'datasetpi', dsau.authors,
'agerange', agerange.ages))
FROM
ndb.datasets AS dts LEFT OUTER JOIN
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
ndb.sites AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid LEFT OUTER JOIN
ndb.collectiontypes as cts ON clu.colltypeid = cts.colltypeid LEFT OUTER JOIN
ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dts.datasetid LEFT OUTER JOIN
(SELECT * FROM doi.agerange(dsid)) AS agerange ON agerange.datasetid = dts.datasetid LEFT OUTER JOIN
ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid LEFT OUTER JOIN
(SELECT * FROM doi.doireturn(dsid)) AS doi ON doi.datasetid = dts.datasetid LEFT OUTER JOIN
(SELECT * FROM doi.datasetauthors(dsid)) AS dsau ON dsau.datasetid = dts.datasetid
WHERE dts.datasetid = ANY(dsid)
$function$;
CREATE OR REPLACE FUNCTION doi.datasetinfo(
dsid integer)
RETURNS TABLE(datasetid integer, dataset json)
LANGUAGE 'sql'
AS $function$
SELECT dts.datasetid,
json_build_object('site', json_build_object('siteid', sts.siteid,
'sitename', sts.sitename,
'sitedescription', sts.sitedescription,
'sitenotes', sts.notes,
'geography', ST_AsGeoJSON(sts.geog,5,2),
'altitude', sts.altitude,
'collectionunitid', clu.collectionunitid,
'collectionunit', clu.collunitname,
'handle', clu.handle,
'unittype', cts.colltype),
'dataset', json_build_object( 'datasetid', dts.datasetid,
'datasettype', dst.datasettype,
'datasetnotes', dts.notes,
'database', cstdb.databasename,
'doi', doi.dois,
'datasetpi', dsau.authors,
'agerange', agerange.ages))
FROM
ndb.datasets AS dts LEFT OUTER JOIN
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
ndb.sites AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid LEFT OUTER JOIN
ndb.collectiontypes as cts ON clu.colltypeid = cts.colltypeid LEFT OUTER JOIN
ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dts.datasetid LEFT OUTER JOIN
(SELECT * FROM doi.agerange(dsid)) AS agerange ON agerange.datasetid = dts.datasetid LEFT OUTER JOIN
ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid LEFT OUTER JOIN
(SELECT * FROM doi.doireturn(dsid)) AS doi ON doi.datasetid = dts.datasetid LEFT OUTER JOIN
(SELECT * FROM doi.datasetauthors(dsid)) AS dsau ON dsau.datasetid = dts.datasetid
WHERE dts.datasetid = dsid
$function$;