Skip to content

Commit d359e3d

Browse files
SimonGoringSimonGoring
committed
Cleaning up functions.
1 parent d372601 commit d359e3d

File tree

7 files changed

+37
-95
lines changed

7 files changed

+37
-95
lines changed

‎function/ts/combinecontacts.sql‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -20,13 +20,13 @@ AS $function$
2020
LOOP
2121
EXECUTE format('
2222
UPDATE %1$s
23-
SET %2$s = $1
24-
WHERE %2$s IN $2'
23+
SET %2$s = _keepcontactid
24+
WHERE %2$s IN _contactidlist'
2525
,rec.tbl, rec.col)
2626
USING _keepcontactid, (SELECT UNNEST(STRING_TO_ARRAY(_contactidlist, '$')))::int;
2727
END LOOP;
2828

29-
EXECUTE format('DELETE FROM %s WHERE %s IN $1', _tbl, _col)
29+
EXECUTE format('DELETE FROM %s WHERE %s IN _keepcontactid', _tbl, _col)
3030
USING (SELECT UNNEST(STRING_TO_ARRAY(_contactidlist,'$')))::int;
3131
END;
3232
$function$

‎function/ts/deleteanalysisunit.sql‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
1-
CREATE OR REPLACE FUNCTION ts.deleteanalysisunit(_analunitid integer)
2-
RETURNS void
3-
LANGUAGE sql
4-
AS $function$
5-
DELETE FROM ndb.analysisunits AS au
6-
WHERE au.analysisunitid = _analunitid;
1+
CREATE OR REPLACE FUNCTION ts.deleteanalysisunit(_analunitid integer) RETURNS void LANGUAGE PLPGSQL AS $function$
2+
DELETE FROM ndb.samples AS sm
3+
WHERE sm.analysisunitid = _analunitid;
4+
5+
DELETE FROM ndb.analysisunits AS au
6+
WHERE au.analysisunitid = _analunitid;
77
$function$

‎function/ts/deletedepenvttype.sql‎

Lines changed: 3 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,4 @@
1-
CREATE OR REPLACE FUNCTION ts.deletedepenvttype(_depenvtid integer)
2-
RETURNS void
3-
LANGUAGE sql
4-
AS $function$
5-
DELETE FROM ndb.depenvttypes AS dvt
6-
WHERE dvt.depenvtid = _depenvtid;
1+
CREATE OR REPLACE FUNCTION ts.deletedepenvttype(_depenvtid integer) RETURNS void LANGUAGE SQL AS $function$
2+
DELETE FROM ndb.depenvttypes AS dvt
3+
WHERE dvt.depenvtid = _depenvtid;
74
$function$

‎function/ts/deletesynonymy.sql‎

Lines changed: 5 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,7 @@
1-
CREATE OR REPLACE FUNCTION ts.deletesynonymy(_synonymyid integer, _contactid integer)
2-
RETURNS void
3-
LANGUAGE sql
4-
AS $function$
5-
DELETE FROM ndb.synonymy AS sy
6-
WHERE sy.synonymyid = _synonymyid;
1+
CREATE OR REPLACE FUNCTION ts.deletesynonymy(_synonymyid integer, _contactid integer) RETURNS void LANGUAGE SQL AS $function$
2+
DELETE FROM ndb.synonymy AS sy
3+
WHERE sy.synonymyid = _synonymyid;
74

8-
INSERT INTO ti.stewardupdates(contactid, tablename, pk1, operation)
9-
VALUES (_contactid, n'synonymy', _synonymyid, n'delete')
5+
INSERT INTO ti.stewardupdates(contactid, tablename, pk1, operation)
6+
VALUES (_contactid, n'synonymy', _synonymyid, n'delete')
107
$function$

‎function/ts/insertanalysisunitaltdepth.sql‎

Lines changed: 1 addition & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,4 @@
1-
CREATE OR REPLACE FUNCTION ts.insertanalysisunitaltdepth(_analysisunitid integer, _altdepthscaleid integer, _altdepth double precision)
2-
RETURNS void
3-
LANGUAGE sql
4-
AS $function$
1+
CREATE OR REPLACE FUNCTION ts.insertanalysisunitaltdepth(_analysisunitid integer, _altdepthscaleid integer, _altdepth double precision) RETURNS void LANGUAGE SQL AS $function$
52
INSERT INTO ndb.analysisunitaltdepths(analysisunitid, altdepthscaleid, altdepth)
63
VALUES (_analysisunitid, _altdepthscaleid, _altdepth)
74
$function$

‎function/ts/updatecollectionunit.sql‎

Lines changed: 16 additions & 65 deletions
Original file line numberDiff line numberDiff line change
@@ -51,72 +51,23 @@ AS $function$
5151
AND table_name = 'collectionunits'
5252
AND (SELECT val FROM goods) IS True;
5353

54-
WITH collunit AS (
55-
SELECT * FROM ndb.collectionunits WHERE collectionunitid = _collunitid
56-
)
57-
5854
UPDATE ndb.collectionunits
5955
SET
60-
handle = CASE WHEN
61-
(_handle <> (SELECT handle FROM collunit) AND
62-
(_handle <> (SELECT handle FROM collunit)) IS NULL) THEN
63-
handle ELSE _handle END,
64-
colltypeid = CASE WHEN
65-
(_colltypeid <> (SELECT colltypeid FROM collunit) AND
66-
(_colltypeid <> (SELECT colltypeid FROM collunit)) IS NULL) THEN
67-
colltypeid ELSE _colltypeid END,
68-
depenvtid = CASE WHEN
69-
(_depenvtid <> (SELECT depenvtid FROM collunit) AND
70-
(_depenvtid <> (SELECT depenvtid FROM collunit)) IS NULL) THEN
71-
depenvtid ELSE _depenvtid END,
72-
collunitname = CASE WHEN
73-
(_collunitname <> (SELECT collunitname FROM collunit) AND
74-
(_collunitname <> (SELECT collunitname FROM collunit)) IS NULL) THEN
75-
collunitname ELSE _collunitname END,
76-
colldate = CASE WHEN
77-
(_colldate <> (SELECT colldate FROM collunit) AND
78-
(_colldate <> (SELECT colldate FROM collunit)) IS NULL) THEN
79-
colldate ELSE _colldate END,
80-
colldevice = CASE WHEN
81-
(_colldevice <> (SELECT colldevice FROM collunit) AND
82-
(_colldevice <> (SELECT colldevice FROM collunit)) IS NULL) THEN
83-
colldevice ELSE _colldevice END,
84-
gpslatitude = CASE WHEN
85-
(_gpslatitude <> (SELECT gpslatitude FROM collunit) AND
86-
(_gpslatitude <> (SELECT gpslatitude FROM collunit)) IS NULL) THEN
87-
gpslatitude ELSE _gpslatitude END,
88-
gpslongitude = CASE WHEN
89-
(_gpslongitude <> (SELECT gpslongitude FROM collunit) AND
90-
(_gpslongitude <> (SELECT gpslongitude FROM collunit)) IS NULL) THEN
91-
gpslongitude ELSE _gpslongitude END,
92-
gpsaltitude = CASE WHEN
93-
(_gpsaltitude <> (SELECT gpsaltitude FROM collunit) AND
94-
(_gpsaltitude <> (SELECT gpsaltitude FROM collunit)) IS NULL) THEN
95-
gpsaltitude ELSE _gpsaltitude END,
96-
waterdepth = CASE WHEN
97-
(_waterdepth <> (SELECT waterdepth FROM collunit) AND
98-
(_waterdepth <> (SELECT waterdepth FROM collunit)) IS NULL) THEN
99-
waterdepth ELSE _waterdepth END,
100-
substrateid = CASE WHEN
101-
(_substrateid <> (SELECT substrateid FROM collunit) AND
102-
(_substrateid <> (SELECT substrateid FROM collunit)) IS NULL) THEN
103-
substrateid ELSE _substrateid END,
104-
slopeaspect = CASE WHEN
105-
(_slopeaspect <> (SELECT slopeaspect FROM collunit) AND
106-
(_slopeaspect <> (SELECT slopeaspect FROM collunit)) IS NULL) THEN
107-
slopeaspect ELSE _slopeaspect END,
108-
slopeangle = CASE WHEN
109-
(_slopeangle <> (SELECT slopeangle FROM collunit) AND
110-
(_slopeangle <> (SELECT slopeangle FROM collunit)) IS NULL) THEN
111-
slopeangle ELSE _slopeangle END,
112-
location = CASE WHEN
113-
(_location <> (SELECT location FROM collunit) AND
114-
(_location <> (SELECT location FROM collunit)) IS NULL) THEN
115-
location ELSE _location END,
116-
notes = CASE WHEN
117-
(_notes <> (SELECT notes FROM collunit) AND
118-
(_notes <> (SELECT notes FROM collunit)) IS NULL) THEN
119-
notes ELSE _notes END;
120-
56+
handle = COALESCE(_handle, handle),
57+
colltypeid = COALESCE(_colltypeid, colltypeid),
58+
depenvtid = COALESCE(_depenvtid, depenvtid),
59+
collunitname = COALESCE(_collunitname, collunitname),
60+
colldate = COALESCE(_colldate, colldate),
61+
colldevice = COALESCE(_colldevice, colldevice),
62+
gpslatitude = COALESCE(_gpslatitude, gpslatitude),
63+
gpslongitude = COALESCE(_gpslongitude, gpslongitude),
64+
gpsaltitude = COALESCE(_gpsaltitude, gpsaltitude),
65+
waterdepth = COALESCE(_waterdepth, waterdepth),
66+
substrateid = COALESCE(_substrateid, substrateid),
67+
slopeaspect = COALESCE(_slopeaspect, slopeaspect),
68+
slopeangle = COALESCE(_slopeangle, slopeangle),
69+
location = COALESCE(_location, location),
70+
notes = COALESCE(_notes, notes)
71+
WHERE collectionunitid = _collunitid;
12172

12273
$function$

‎function/ts/updatelakeparam.sql‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -12,8 +12,8 @@ BEGIN
1212

1313
IF _value IS NOT NULL THEN
1414
IF _nparam IS NOT NULL THEN /* parameter in Neotoma, need to change */
15-
UPDATE ndb.lakeparameters
16-
SET value = value WHERE (siteid = _siteid) AND (lakeparameterid = lakeparameterid);
15+
UPDATE ndb.lakeparameters AS lp
16+
SET value = value WHERE (siteid = _siteid) AND (lp.lakeparameterid = lakeparameterid);
1717
INSERT INTO ti.stewardupdates(contactid, tablename, pk1, pk2, operation, columnname)
1818
VALUES (_stewardcontactid, 'lakeparameters',_siteid, lakeparameterid, 'update', 'value');
1919
ELSE
@@ -24,7 +24,7 @@ BEGIN
2424
END IF;
2525
ELSE
2626
IF _nparam IS NOT NULL THEN /* parameter in Neotoma, need to delete */
27-
DELETE FROM ndb.lakeparameters
27+
DELETE FROM ndb.lakeparameters
2828
WHERE (siteid = _siteid) AND (lakeparameterid = lakeparameterid);
2929
INSERT INTO ti.stewardupdates(contactid, tablename, pk1, pk2, operation)
3030
VALUES (_stewardcontactid, 'lakeparameters', _siteid, lakeparameterid, 'delete');

0 commit comments

Comments
 (0)