-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdatasetauthors.sql
More file actions
31 lines (30 loc) · 1.41 KB
/
datasetauthors.sql
File metadata and controls
31 lines (30 loc) · 1.41 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
CREATE OR REPLACE FUNCTION doi.datasetauthors(dsid integer)
RETURNS TABLE(datasetid integer, authors jsonb)
LANGUAGE sql
AS $function$
SELECT dst.datasetid, jsonb_agg(jsonb_build_object('contactid', cnt.contactid,
'contactname', cnt.contactname,
'familyname', cnt.familyname,
'firstname', cnt.givennames,
'initials', cnt.leadinginitials)) AS authors
FROM ndb.datasets AS dst
JOIN ndb.datasetpis AS dspi ON dspi.datasetid = dst.datasetid LEFT OUTER JOIN
ndb.contacts AS cnt ON cnt.contactid = dspi.contactid
WHERE dst.datasetid = dsid
GROUP BY dst.datasetid
$function$;
CREATE OR REPLACE FUNCTION doi.datasetauthors(dsid integer[])
RETURNS TABLE(datasetid integer, authors jsonb)
LANGUAGE sql
AS $function$
SELECT dst.datasetid, jsonb_agg(jsonb_build_object('contactid', cnt.contactid,
'contactname', cnt.contactname,
'familyname', cnt.familyname,
'firstname', cnt.givennames,
'initials', cnt.leadinginitials)) AS authors
FROM ndb.datasets AS dst
JOIN ndb.datasetpis AS dspi ON dspi.datasetid = dst.datasetid LEFT OUTER JOIN
ndb.contacts AS cnt ON cnt.contactid = dspi.contactid
WHERE dst.datasetid = ANY(dsid)
GROUP BY dst.datasetid
$function$;