-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathgetcontactlinks.sql
More file actions
75 lines (63 loc) · 2.51 KB
/
getcontactlinks.sql
File metadata and controls
75 lines (63 loc) · 2.51 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
CREATE OR REPLACE FUNCTION ti.getcontactlinks(_contactid integer)
RETURNS TABLE(tablename character varying, number integer)
LANGUAGE plpgsql
AS $function$
DECLARE
c int;
BEGIN
DROP TABLE IF EXISTS linkedtables;
CREATE TEMP TABLE linkedtables(
linkid serial primary key,
tablename varchar(255),
number int
);
c := (SELECT count(contactid) AS count FROM ndb.chronologies WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('chronologies', c);
END IF;
c := (SELECT count(contactid) AS count FROM ndb.collectors WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('collectors', c);
END IF;
c := (SELECT count(contactid) AS count FROM ndb.constituentdatabases WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('constituentdatabases', c);
END IF;
c := (SELECT count(contactid) AS count FROM ndb.dataprocessors WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('dataprocessors', c);
END IF;
c := (SELECT count(contactid) AS count FROM ndb.datasetpis WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('datasetpis', c);
END IF;
c := (SELECT count(contactid) AS count FROM ndb.publicationauthors WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('publicationauthors', c);
END IF;
c := (SELECT count(contactid) AS count FROM ndb.sampleanalysts WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('sampleanalysts', c);
END IF;
c := (SELECT count(contactid) AS count FROM ndb.siteimages WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('siteimages', c);
END IF;
c := (SELECT count(contactid) AS count FROM ti.stewards WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('stewards', c);
END IF;
c := (SELECT count(contactid) AS count FROM ti.stewardupdates WHERE contactid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('stewardupdates', c);
END IF;
c := (SELECT count(validatorid) AS count FROM ndb.taxa WHERE validatorid = _contactid);
IF c > 0 THEN
INSERT INTO linkedtables(tablename, number) VALUES('taxa', c);
END IF;
RETURN QUERY
SELECT linkedtables.tablename, linkedtables.number
FROM linkedtables;
DROP TABLE IF EXISTS linkedtables;
END;
$function$