Skip to content

Commit f53d7bd

Browse files
SimonGoringSimonGoring
committed
A bunch of functions in the DB are not on Git.
1 parent 3bc3b31 commit f53d7bd

8 files changed

+176
-0
lines changed

‎function/ap/getauthors.sql‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
CREATE OR REPLACE FUNCTION ap.getauthors()
2+
RETURNS TABLE(contactid integer, contactname character varying)
3+
LANGUAGE sql
4+
AS $function$
5+
6+
With _authortable AS (
7+
8+
select distinct pa.contactid, c.contactname
9+
from ndb.publicationauthors as pa inner join
10+
ndb.contacts as c on c.contactid = pa.contactid
11+
),
12+
13+
_contactaliases AS (
14+
select c.contactid, c.contactname
15+
from ndb.contacts as c
16+
where c.contactid != c.aliasid and c.aliasid > 0 and c.aliasid in (select contactid from _authortable)
17+
)
18+
19+
Select * from _authortable
20+
21+
union all
22+
23+
select * from _contactaliases
24+
25+
26+
$function$

‎function/ap/getdeptenvtypes.sql‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
CREATE OR REPLACE FUNCTION ap.getdeptenvtypes(_depenvtid integer)
2+
RETURNS TABLE(depenvtid integer, depenvt character varying, children integer)
3+
LANGUAGE sql
4+
AS $function$
5+
6+
WITH
7+
nodes AS (
8+
select det.depenvtid, det.depenvthigherid, det.depenvt
9+
from ndb.depenvttypes as det
10+
where det.depenvthigherid = _depenvtid and det.depenvtid != _depenvtid
11+
),
12+
-- site info needed by the web application
13+
children as (
14+
select det.depenvthigherid, count(det.depenvtid)::integer as "children"
15+
from ndb.depenvttypes as det
16+
where det.depenvthigherid in (select depenvtid from nodes)
17+
group by depenvthigherid
18+
)
19+
20+
select n.depenvtid, n.depenvt, c.children
21+
from nodes as n left join
22+
children as c on n.depenvtid = c.depenvthigherid
23+
24+
$function$

‎function/ap/getelementtypes.sql‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
CREATE OR REPLACE FUNCTION ap.getelementtypes(_taxagroupid character varying)
2+
RETURNS TABLE(elementtypeid integer, elementtype character varying)
3+
LANGUAGE sql
4+
AS $function$
5+
select et.elementtypeid, et.elementtype
6+
from ndb.elementtypes as et inner join
7+
ndb.elementtaxagroups as etg on etg.elementtypeid = et.elementtypeid
8+
where etg.taxagroupid = _taxagroupid
9+
order by elementtype
10+
$function$

‎function/ap/getelementtypesbytaxonid.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
CREATE OR REPLACE FUNCTION ap.getelementtypesbytaxonid(_taxonid integer)
2+
RETURNS TABLE(elementtypeid integer, elementtype character varying)
3+
LANGUAGE sql
4+
AS $function$
5+
select distinct et.elementtypeid, et.elementtype
6+
from ndb.taxa inner join
7+
ndb.variables on ndb.taxa.taxonid = ndb.variables.taxonid inner join
8+
ndb.data on ndb.variables.variableid = ndb.data.variableid left outer join
9+
ndb.variableelements on ndb.variables.variableelementid = ndb.variableelements.variableelementid inner join
10+
ndb.elementtypes as et on ndb.variableelements.elementtypeid = et.elementtypeid
11+
where (ndb.taxa.taxonid = _taxonid)
12+
order by elementtype
13+
$function$

‎function/ap/getgeochrondatasetbyassociateddatasetid.sql‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
CREATE OR REPLACE FUNCTION ap.getgeochrondatasetbyassociateddatasetid(_datasetid integer)
2+
RETURNS TABLE(sampleid integer, geochrontype character varying, agetype character varying, depth double precision, thickness double precision, age double precision, errorolder double precision, erroryounger double precision, infinite boolean, delta13c double precision, labnumber character varying, materialdated character varying, notes text)
3+
LANGUAGE sql
4+
AS $function$
5+
select ndb.geochronology.sampleid, ndb.geochrontypes.geochrontype, ndb.agetypes.agetype, ndb.analysisunits.depth,
6+
ndb.analysisunits.thickness, ndb.geochronology.age, ndb.geochronology.errorolder, ndb.geochronology.erroryounger,
7+
ndb.geochronology.infinite, ndb.geochronology.delta13c, ndb.geochronology.labnumber, ndb.geochronology.materialdated, ndb.geochronology.notes
8+
from ndb.geochronology inner join
9+
ndb.samples on ndb.geochronology.sampleid = ndb.samples.sampleid inner join
10+
ndb.datasets on ndb.samples.datasetid = ndb.datasets.datasetid inner join
11+
ndb.geochrontypes on ndb.geochronology.geochrontypeid = ndb.geochrontypes.geochrontypeid inner join
12+
ndb.agetypes on ndb.geochronology.agetypeid = ndb.agetypes.agetypeid inner join
13+
ndb.analysisunits on ndb.samples.analysisunitid = ndb.analysisunits.analysisunitid
14+
where (ndb.datasets.datasetid = _datasetid and (ndb.datasets.datasettypeid = 1));
15+
$function$

‎function/ap/gettaphonomictypes.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
CREATE OR REPLACE FUNCTION ap.gettaphonomictypes(_taphonomicsystemid integer)
2+
RETURNS TABLE(taphonomictypeid integer, taphonomictype character varying)
3+
LANGUAGE sql
4+
AS $function$
5+
select tt.taphonomictypeid, tt.taphonomictype
6+
from ndb.taphonomictypes as tt
7+
where tt.taphonomicsystemid = _taphonomicsystemid
8+
order by taphonomictype
9+
$function$

‎function/ap/searchsitename.sql‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
CREATE OR REPLACE FUNCTION ap.searchsitename(_sitename character varying)
2+
RETURNS TABLE(siteid integer, geog geography, datasetid integer, datasettype character varying, collunithandle character varying, collunitname character varying, databasename character varying, sitename character varying, sitedescription text, longitudeeast double precision, latitudenorth double precision, longitudewest double precision, latitudesouth double precision, minage integer, maxage integer, ageyoungest integer, ageoldest integer, precedence integer)
3+
LANGUAGE sql
4+
AS $function$
5+
6+
SELECT
7+
s.siteid,
8+
s.geog as "geog",
9+
ndb.datasets.datasetid,
10+
ndb.datasettypes.datasettype,
11+
ndb.collectionunits.handle as "collunithandle",
12+
ndb.collectionunits.collunitname,
13+
ndb.constituentdatabases.databasename,
14+
s.sitename,
15+
s.sitedescription,
16+
s.longitudeeast,
17+
s.latitudenorth,
18+
s.longitudewest,
19+
s.latitudesouth,
20+
null::integer as minage,
21+
null::integer as maxage,
22+
ch.ageboundyounger as ageyoungest,
23+
ch.ageboundolder as ageoldest,
24+
at.precedence
25+
FROM
26+
ndb.datasets inner join
27+
ndb.datasettypes on ndb.datasettypes.datasettypeid = ndb.datasets.datasettypeid left join
28+
ndb.datasetdatabases on ndb.datasetdatabases.datasetid = ndb.datasets.datasetid left join
29+
ndb.constituentdatabases on ndb.constituentdatabases.databaseid = ndb.datasetdatabases.databaseid inner join
30+
ndb.collectionunits on ndb.datasets.collectionunitid = ndb.collectionunits.collectionunitid inner join
31+
ndb.sites as s on ndb.collectionunits.siteid = s.siteid left join
32+
ndb.chronologies as ch on ch.collectionunitid = ndb.collectionunits.collectionunitid left join
33+
ndb.agetypes as at on at.agetypeid = ch.agetypeid
34+
35+
where s.sitename like _sitename
36+
order by siteid, datasetid, precedence;
37+
38+
$function$

‎function/ti/getnextpublicationbyidandpubtypeid.sql‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
CREATE OR REPLACE FUNCTION ti.getnextpublicationbyidandpubtypeid(_publicationid integer, _pubtypeid integer)
2+
RETURNS TABLE(publicationid integer, pubtypeid integer, year character varying, citation text, articletitle text, journal text, volume character varying, issue character varying, pages character varying, citationnumber character varying, doi character varying, booktitle text, numvolumes character varying, edition character varying, volumetitle text, seriestitle text, seriesvolume character varying, publisher character varying, url text, city character varying, state character varying, country character varying, originallanguage character varying, notes text)
3+
LANGUAGE plpgsql
4+
AS $function$
5+
DECLARE
6+
nextpubid int;
7+
8+
BEGIN
9+
nextpubid := (SELECT MIN (a.publicationid) FROM ndb.publications a WHERE a.publicationid > $1 and a.pubtypeid = $2);
10+
11+
RETURN QUERY
12+
SELECT
13+
pub.publicationid,
14+
pub.pubtypeid,
15+
pub.year,
16+
pub.citation,
17+
pub.articletitle,
18+
pub.journal,
19+
pub.volume,
20+
pub.issue,
21+
pub.pages,
22+
pub.citationnumber,
23+
pub.doi,
24+
pub.booktitle,
25+
pub.numvolumes,
26+
pub.edition,
27+
pub.volumetitle,
28+
pub.seriestitle,
29+
pub.seriesvolume,
30+
pub.publisher,
31+
pub.url,
32+
pub.city,
33+
pub.state,
34+
pub.country,
35+
pub.originallanguage,
36+
pub.notes
37+
FROM ndb.publications pub
38+
WHERE pub.publicationid = nextpubid;
39+
40+
END;
41+
$function$

0 commit comments

Comments
 (0)