chore(distro-ddl): regenerate Postgres DDL with upsert triggers
MODIFY
distro/data/src/main/resources/ddls/apiman_postgresql9.ddl
distro/data/src/main/resources/ddls/apiman_postgresql9.ddl
@@ -440,7 +440,7 @@ CREATE INDEX api_version_discoverability_index ON discoverability(org_id, api_id
-- ApiPlan
---- Insert
-CREATE PROCEDURE insert_apiplan_into_discoverability(api_plans) AS $$
+CREATE OR REPLACE PROCEDURE upsert_apiplan_into_discoverability(api_plans) AS $$
WITH Api_Version_CTE (api_org_id, api_id, api_version)
AS
(
@@ -464,38 +464,18 @@ CREATE PROCEDURE insert_apiplan_into_discoverability(api_plans) AS $$
$1.version,
$1.discoverability
FROM Api_Version_CTE
-$$ LANGUAGE SQL;
-
----- Update
-CREATE PROCEDURE update_apiplan_into_discoverability(api_plans) AS $$
- WITH Api_Version_CTE (api_org_id, api_id, api_version)
- AS
- (
- SELECT av.api_org_id AS api_org_id, av.api_id AS api_id, av.version AS api_version
- FROM api_versions av
- WHERE av.id = $1.api_version_id
- )
- UPDATE discoverability
- SET (org_id, api_id, api_version, plan_id, plan_version, discoverability) = (
- Api_Version_CTE.api_org_id,
- Api_Version_CTE.api_id,
- Api_Version_CTE.api_version,
+ ON CONFLICT (id) DO UPDATE SET (org_id, api_id, api_version, plan_id, plan_version, discoverability) = (
+ (select api_org_id from Api_Version_CTE),
+ (select api_id from Api_Version_CTE),
+ (select api_version from Api_Version_CTE),
$1.plan_id,
$1.version,
$1.discoverability
- )
- FROM Api_Version_CTE
- WHERE id = CONCAT_WS(':',
- Api_Version_CTE.api_org_id,
- Api_Version_CTE.api_id,
- Api_Version_CTE.api_version,
- $1.plan_id,
- $1.version
- )
+ );
$$ LANGUAGE SQL;
---- Delete
-CREATE PROCEDURE delete_apiplan_from_discoverability(api_plans) AS $$
+CREATE OR REPLACE PROCEDURE delete_apiplan_from_discoverability(api_plans) AS $$
WITH Api_Version_CTE (api_org_id, api_id, api_version)
AS
(
@@ -512,12 +492,11 @@ CREATE PROCEDURE delete_apiplan_from_discoverability(api_plans) AS $$
$1.plan_id,
$1.version
);
-
$$ LANGUAGE SQL;
-- ApiVersion
---- Insert
-CREATE PROCEDURE insert_apiversion_into_discoverability(api_versions) AS $$
+CREATE OR REPLACE PROCEDURE upsert_apiversion_into_discoverability(api_versions) AS $$
INSERT INTO discoverability(id, org_id, api_id, api_version, plan_id, plan_version, discoverability)
VALUES (
CONCAT_WS(':', $1.api_org_id, $1.api_id, $1.version),
@@ -527,14 +506,8 @@ CREATE PROCEDURE insert_apiversion_into_discoverability(api_versions) AS $$
NULL,
NULL,
$1.discoverability
- );
-
-$$ LANGUAGE SQL;
-
----- Update
-CREATE PROCEDURE update_apiversion_into_discoverability(api_versions) AS $$
- UPDATE discoverability
- SET (org_id, api_id, api_version, plan_id, plan_version, discoverability) = (
+ )
+ ON CONFLICT (id) DO UPDATE SET (org_id, api_id, api_version, plan_id, plan_version, discoverability) = (
$1.api_org_id,
$1.api_id,
$1.version,
@@ -542,14 +515,11 @@ CREATE PROCEDURE update_apiversion_into_discoverability(api_versions) AS $$
NULL,
$1.discoverability
)
- WHERE id = CONCAT_WS(':', $1.api_org_id, $1.api_id, $1.version);
-
$$ LANGUAGE SQL;
---- Delete
-CREATE PROCEDURE delete_apiversion_from_discoverability(api_versions) AS $$
+CREATE OR REPLACE PROCEDURE delete_apiversion_from_discoverability(api_versions) AS $$
DELETE FROM discoverability d WHERE d.id = CONCAT_WS(':', $1.api_org_id, $1.api_id, $1.version);
-
$$ LANGUAGE SQL;
-- API Plans
@@ -557,25 +527,14 @@ CREATE OR REPLACE FUNCTION api_plan_discoverability_trigger_func() RETURNS TRIGG
BEGIN
IF (TG_OP = 'DELETE') THEN
CALL delete_apiplan_from_discoverability(OLD);
-
RETURN OLD;
-
-ELSIF (TG_OP = 'UPDATE') THEN
- CALL update_apiplan_into_discoverability(NEW);
-
+ ELSIF (TG_OP = 'UPDATE' or TG_OP = 'INSERT') THEN
+ CALL upsert_apiplan_into_discoverability(NEW);
RETURN NEW;
-
-ELSIF (TG_OP = 'INSERT') THEN
- CALL insert_apiplan_into_discoverability(NEW);
-
-RETURN NEW;
-
END IF;
RETURN NULL;
-
END;
-
$$;
-- API Versions
@@ -583,33 +542,23 @@ CREATE OR REPLACE FUNCTION api_version_discoverability_trigger_func() RETURNS TR
BEGIN
IF (TG_OP = 'DELETE') THEN
CALL delete_apiversion_from_discoverability(OLD);
-
RETURN OLD;
-
-ELSIF (TG_OP = 'UPDATE') THEN
- CALL update_apiversion_into_discoverability(NEW);
-
-RETURN NEW;
-
-ELSIF (TG_OP = 'INSERT') THEN
- CALL insert_apiversion_into_discoverability(NEW);
-
+ ELSIF (TG_OP = 'UPDATE' or TG_OP = 'INSERT') THEN
+ CALL upsert_apiversion_into_discoverability(NEW);
RETURN NEW;
-
END IF;
RETURN NULL;
-
END;
-
$$;
-- Triggers
--- Api Plans
+
+DROP TRIGGER IF EXISTS api_plan_discoverability_trigger ON api_plans;
CREATE TRIGGER api_plan_discoverability_trigger AFTER INSERT OR UPDATE OR DELETE ON api_plans FOR EACH ROW EXECUTE PROCEDURE api_plan_discoverability_trigger_func();
--- Api Versions
+DROP TRIGGER IF EXISTS api_version_discoverability_trigger ON api_versions;
CREATE TRIGGER api_version_discoverability_trigger AFTER INSERT OR UPDATE OR DELETE ON api_versions FOR EACH ROW EXECUTE PROCEDURE api_version_discoverability_trigger_func();
-
--- End (postgres sometimes doesn't like the last line to be a trigger function, so this is just to pad it out).;
-
+-- End (postgres sometimes doesn't like the last line to be a trigger function, so this is just to pad it out).

chore(distro-ddl): regenerate Postgres DDL with upsert triggers