-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy path01_datasetinfo.sql
More file actions
115 lines (111 loc) · 7.26 KB
/
01_datasetinfo.sql
File metadata and controls
115 lines (111 loc) · 7.26 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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
CREATE OR REPLACE FUNCTION doi.datasetinfo(dsid INT[])
RETURNS TABLE(datasetid integer, site jsonb)
LANGUAGE sql
AS $function$
WITH siteid AS (
SELECT clu.siteid AS siteid
FROM
ndb.datasets AS dts
LEFT OUTER JOIN ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid
WHERE dts.datasetid = ANY(dsid)
),
siteobj AS (
SELECT *
FROM
ap.siteobject((SELECT array_agg(siteid) FROM siteid))
)
SELECT DISTINCT dts.datasetid,
sts.site ||
jsonb_build_object('collectionunit', json_build_object('collectionunitid', clu.collectionunitid,
'depositionalenvironment', dvt.depenvt,
'collectionunit', clu.collunitname,
'handle', clu.handle,
'collunittype', cts.colltype,
'colldate', clu.colldate,
'waterdepth', clu.waterdepth,
'notes', clu.notes,
'collectiondevice', clu.colldevice,
'location', clu.location,
'gpslocation', json_build_object('latitude', clu.gpslatitude,
'longitude', clu.gpslongitude,
'gpsaltitude', clu.gpsaltitude,
'gpserror', clu.gpserror)),
'dataset', json_build_object('datasetid', dts.datasetid,
'datasettype', dst.datasettype,
'datasetname', dts.datasetname,
'datasetnotes', dts.notes,
'database', cstdb.databasename,
'doi', doi.dois,
'recdatecreated', dts.recdatecreated,
'datasetpi', dsau.authors,
'agerange', agerange.ages)) AS site
FROM
ndb.datasets AS dts
LEFT OUTER JOIN ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid
LEFT OUTER JOIN ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid
LEFT OUTER JOIN siteobj 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 ndb.collectors AS col ON col.collectionunitid = clu.collectionunitid
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, site jsonb)
LANGUAGE 'sql'
AS $function$
WITH siteid AS (
SELECT clu.siteid AS siteid
FROM
ndb.datasets AS dts
LEFT OUTER JOIN ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid
WHERE dts.datasetid = dsid
),
siteobj AS (
SELECT *
FROM
ap.siteobject((SELECT siteid FROM siteid))
)
SELECT DISTINCT dts.datasetid,
sts.site ||
jsonb_build_object('collectionunit', json_build_object('collectionunitid', clu.collectionunitid,
'depositionalenvironment', dvt.depenvt,
'collectionunit', clu.collunitname,
'handle', clu.handle,
'collunittype', cts.colltype,
'colldate', clu.colldate,
'waterdepth', clu.waterdepth,
'notes', clu.notes,
'collectiondevice', clu.colldevice,
'gpslocation', json_build_object('latitude', clu.gpslatitude,
'longitude', clu.gpslongitude,
'gpsaltitude', clu.gpsaltitude,
'gpserror', clu.gpserror),
'location', clu.location),
'dataset', json_build_object( 'datasetid', dts.datasetid,
'datasettype', dst.datasettype,
'datasetname', dts.datasetname,
'datasetnotes', dts.notes,
'database', cstdb.databasename,
'doi', doi.dois,
'datasetpi', dsau.authors,
'agerange', agerange.ages)) AS site
FROM
ndb.datasets AS dts LEFT OUTER JOIN
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
siteobj AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid 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$;