Skip to content

Commit 87a38dc

Browse files
SimonGoringSimonGoring
committed
Changed the replacepubid function to a plpgsql function.
This uses the same kind of model as the `combinecontacts` function now. Probably better (in the end) to use foreign keys, but oh well.
1 parent a49660b commit 87a38dc

File tree

1 file changed

+43
-105
lines changed

1 file changed

+43
-105
lines changed

‎function/ts/updatereplacepublicationid.sql‎

Lines changed: 43 additions & 105 deletions
Original file line numberDiff line numberDiff line change
@@ -1,109 +1,47 @@
11
CREATE OR REPLACE FUNCTION updatereplacepublicationid(_keeppubid integer, _deposepubid integer)
22
RETURNS TABLE(id integer,
33
result character varying)
4-
LANGUAGE SQL
4+
LANGUAGE plpgsql
55
AS $function$
6-
7-
/* Replace @DEPOSEPUBID with @KEEPPUBID and delete @DEPOSEPUBID */
8-
9-
10-
WITH goodpub AS (
11-
SELECT pub.publicationid, 'keep'
12-
FROM ndb.publications AS pub WHERE (pub.publicationid = _keeppubid)
13-
UNION
14-
SELECT pub.publicationid, 'drop'
15-
FROM ndb.publications AS pub WHERE (pub.publicationid = _deposepubid)
16-
17-
)
18-
DECLARE @N int
19-
20-
SET @N = (SELECT COUNT(*) FROM NDB.Publications WHERE (PublicationID = @KEEPPUBID))
21-
IF (@N = 0)
22-
BEGIN
23-
INSERT INTO @RESULTS SELECT (CONCAT(N'KEEPPUBID ' ,CAST(@KEEPPUBID AS nvarchar), N' does not exist. Procedure aborted.'))
24-
SELECT Result FROM @RESULTS
25-
RETURN
26-
END
27-
28-
SET @N = (SELECT COUNT(*) FROM NDB.Publications WHERE (PublicationID = @DEPOSEPUBID))
29-
IF (@N = 0)
30-
BEGIN
31-
INSERT INTO @RESULTS SELECT (CONCAT(N'DEPOSEPUBID ' ,CAST(@DEPOSEPUBID AS nvarchar), N' does not exist. Procedure aborted.'))
32-
SELECT Result FROM @RESULTS
33-
RETURN
34-
END
35-
36-
37-
SET @N = (SELECT COUNT(*) FROM NDB.Publications WHERE (PublicationID = @DEPOSEPUBID))
38-
39-
SET @N = (SELECT COUNT(*) FROM NDB.CalibrationCurves WHERE (PublicationID = @DEPOSEPUBID))
40-
IF (@N > 0)
41-
BEGIN
42-
UPDATE NDB.CalibrationCurves
43-
SET NDB.CalibrationCurves.PublicationID = @KEEPPUBID
44-
WHERE (NDB.CalibrationCurves.PublicationID = @DEPOSEPUBID)
45-
END
46-
INSERT INTO @RESULTS SELECT (CONCAT(N'Records updated from CalibrationCurves = .' ,CAST(@N AS nvarchar)))
47-
48-
SET @N = (SELECT COUNT(*) FROM NDB.DatasetPublications WHERE (PublicationID = @DEPOSEPUBID))
49-
IF (@N > 0)
50-
BEGIN
51-
UPDATE NDB.DatasetPublications
52-
SET NDB.DatasetPublications.PublicationID = @KEEPPUBID
53-
WHERE (NDB.DatasetPublications.PublicationID = @DEPOSEPUBID)
54-
END
55-
INSERT INTO @RESULTS SELECT (CONCAT(N'Records updated from DatasetPublications = .' ,CAST(@N AS nvarchar)))
56-
57-
SET @N = (SELECT COUNT(*) FROM NDB.EventPublications WHERE (PublicationID = @DEPOSEPUBID))
58-
IF (@N > 0)
59-
BEGIN
60-
UPDATE NDB.EventPublications
61-
SET NDB.EventPublications.PublicationID = @KEEPPUBID
62-
WHERE (NDB.EventPublications.PublicationID = @DEPOSEPUBID)
63-
END
64-
INSERT INTO @RESULTS SELECT (CONCAT(N'Records updated from EventPublications = .' ,CAST(@N AS nvarchar)))
65-
66-
SET @N = (SELECT COUNT(*) FROM NDB.ExternalPublications WHERE (PublicationID = @DEPOSEPUBID))
67-
IF (@N > 0)
68-
BEGIN
69-
UPDATE NDB.ExternalPublications
70-
SET NDB.ExternalPublications.PublicationID = @KEEPPUBID
71-
WHERE (NDB.ExternalPublications.PublicationID = @DEPOSEPUBID)
72-
END
73-
INSERT INTO @RESULTS SELECT (CONCAT(N'Records updated from ExternalPublications = .' ,CAST(@N AS nvarchar)))
74-
75-
SET @N = (SELECT COUNT(*) FROM NDB.FormTaxa WHERE (PublicationID = @DEPOSEPUBID))
76-
IF (@N > 0)
77-
BEGIN
78-
UPDATE NDB.FormTaxa
79-
SET NDB.FormTaxa.PublicationID = @KEEPPUBID
80-
WHERE (NDB.FormTaxa.PublicationID = @DEPOSEPUBID)
81-
END
82-
INSERT INTO @RESULTS SELECT (CONCAT(N'Records updated from FormTaxa = .' ,CAST(@N AS nvarchar)))
83-
84-
SET @N = (SELECT COUNT(*) FROM NDB.GeochronPublications WHERE (PublicationID = @DEPOSEPUBID))
85-
IF (@N > 0)
86-
BEGIN
87-
UPDATE NDB.GeochronPublications
88-
SET NDB.GeochronPublications.PublicationID = @KEEPPUBID
89-
WHERE (NDB.GeochronPublications.PublicationID = @DEPOSEPUBID)
90-
END
91-
INSERT INTO @RESULTS SELECT (CONCAT(N'Records updated from GeochronPublications = .' ,CAST(@N AS nvarchar)))
92-
93-
SET @N = (SELECT COUNT(*) FROM NDB.RelativeAgePublications WHERE (PublicationID = @DEPOSEPUBID))
94-
IF (@N > 0)
95-
BEGIN
96-
UPDATE NDB.RelativeAgePublications
97-
SET NDB.RelativeAgePublications.PublicationID = @KEEPPUBID
98-
WHERE (NDB.RelativeAgePublications.PublicationID = @DEPOSEPUBID)
99-
END
100-
INSERT INTO @RESULTS SELECT (CONCAT(N'Records updated from RelativeAgePublications = .' ,CAST(@N AS nvarchar)))
101-
102-
DELETE FROM NDB.Publications
103-
WHERE PublicationID = @DEPOSEPUBID
104-
INSERT INTO @RESULTS SELECT (CONCAT(N'PublicationdID ', CAST(@DEPOSEPUBID AS nvarchar), N' deleted from Publications table.'))
105-
106-
SELECT Result FROM @RESULTS
107-
108-
109-
GO
6+
DECLARE
7+
rec RECORD;
8+
BEGIN
9+
CREATE TEMP TABLE resulting AS (
10+
SELECT c.conrelid::regclass::varchar AS tbl,
11+
NULL::varchar AS updated
12+
FROM pg_catalog.pg_attribute AS a1
13+
JOIN pg_catalog.pg_constraint AS c ON c.confrelid = a1.attrelid
14+
AND c.confkey = ARRAY[a1.attnum]
15+
JOIN pg_catalog.pg_attribute AS a2 ON a2.attrelid = c.conrelid
16+
AND a2.attnum = c.conkey[1]
17+
WHERE quote_ident(a2.attname) = 'publicationid'
18+
AND c.contype = 'f');
19+
20+
FOR rec IN
21+
SELECT
22+
c.conrelid::regclass AS tbl,
23+
quote_ident(a2.attname) AS col
24+
FROM pg_catalog.pg_attribute AS a1
25+
JOIN pg_catalog.pg_constraint AS c ON c.confrelid = a1.attrelid
26+
AND c.confkey = ARRAY[a1.attnum]
27+
JOIN pg_catalog.pg_attribute AS a2 ON a2.attrelid = c.conrelid
28+
AND a2.attnum = c.conkey[1]
29+
WHERE quote_ident(a2.attname) = 'publicationid'
30+
AND c.contype = 'f'
31+
LOOP
32+
EXECUTE format('
33+
UPDATE resulting
34+
SET updated = (SELECT ''Replaced ''|| COUNT(*) || '' to %1$s'' AS result FROM %1$s WHERE %2$s = %4$s)
35+
WHERE tbl::varchar = %3$s;'
36+
,rec.tbl, rec.col, quote_literal(rec.tbl), _deposepubid);
37+
EXECUTE format('
38+
UPDATE %1$s
39+
SET %2$s = %3$s
40+
WHERE %2$s = %4$s;'
41+
,rec.tbl, rec.col, _keeppubid, _deposepubid);
42+
END LOOP;
43+
RETURN QUERY
44+
SELECT NULL::int AS id, updated AS result
45+
FROM resulting;
46+
END;
47+
$function$

0 commit comments

Comments
 (0)