Code: Select all
2016-05-27 17:14:13 EEST ERROR: duplicate key value violates unique constraint "ix_servicedeploymenthash"
2016-05-27 17:14:13 EEST DETAIL: Key (servicedeploymenthash)=(57597b83-6444-a92a-16b6-3776f6302a83) already exists.
2016-05-27 17:14:13 EEST CONTEXT: SQL statement "INSERT INTO ServiceDeploymentsTable (ServiceDeploymentHash, SiteName, RelativeServicePath, RelativeApplicationPath, ServiceName, ServiceNamespace) VALUES (p_serviceDeploymentHash, p_siteName, p_relativeServicePath, p_relativeApplicationPath, p_serviceName, p_serviceNamespace) RETURNING id"
PL/pgSQL function createservicedeployment(uuid,character varying,character varying,character varying,character varying,character varying) line 6 at SQL statement
Code: Select all
SELECT Id INTO p_serviceDeploymentId FROM ServiceDeploymentsTable WHERE ServiceDeploymentHash = p_serviceDeploymentHash;
IF p_serviceDeploymentId IS NULL THEN
INSERT INTO ServiceDeploymentsTable (ServiceDeploymentHash, SiteName, RelativeServicePath, RelativeApplicationPath, ServiceName, ServiceNamespace) VALUES (p_serviceDeploymentHash, p_siteName, p_relativeServicePath, p_relativeApplicationPath, p_serviceName, p_serviceNamespace) RETURNING id INTO p_serviceDeploymentId;
END IF;
So we suggest doing following (in PostgreSQL 9.5 or higher). Leverage functionality of ON CONFLICT statement and rewrite procedure as
Code: Select all
CREATE OR REPLACE FUNCTION public.createservicedeployment(
p_servicedeploymenthash uuid,
p_sitename character varying,
p_relativeservicepath character varying,
p_relativeapplicationpath character varying,
p_servicename character varying,
p_servicenamespace character varying)
RETURNS bigint AS
$BODY$
DECLARE p_serviceDeploymentId BIGINT;
DECLARE p_rowcount INT;
BEGIN
INSERT INTO ServiceDeploymentsTable (ServiceDeploymentHash, SiteName, RelativeServicePath, RelativeApplicationPath, ServiceName, ServiceNamespace)
VALUES (p_serviceDeploymentHash, p_siteName, p_relativeServicePath, p_relativeApplicationPath, p_serviceName, p_serviceNamespace)
ON CONFLICT (ServiceDeploymentHash) DO NOTHING
RETURNING id INTO p_serviceDeploymentId;
GET DIAGNOSTICS p_rowcount = ROW_COUNT;
IF(p_rowcount = 0)
THEN
SELECT Id INTO p_serviceDeploymentId FROM ServiceDeploymentsTable WHERE ServiceDeploymentHash = p_serviceDeploymentHash;
END IF;
RETURN p_serviceDeploymentId;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.createservicedeployment(uuid, character varying, character varying, character varying, character varying, character varying)
OWNER TO postgres;