Vous n'êtes pas identifié(e).
Pages : 1
Bonjour à tous,
Je suis débutant et je bloque encore sur du remplissage automatique :
1- Je veux remplir des champs d'une table en fonction de la valeur d'autre champ
2- Je recherche comment écrire une contrainte qui pourra vérifier que mes données ne se chevauchent pas (champ mFrom et mTo) pour un même HoleID
3- Renvoyer un message claire et précis lorsqu'une contrainte est violée
J'ai pu écrire les différents scripts relatifs à ma préoccupation numéro 1 mais le code 4 lui ne fonctionne pas du tout car il bloque.
Voici les différents scripts que j'ai pu écrire :
Code 1 :
CREATE TABLE "DHSample_Test"
(
"SampleID" integer NOT NULL,
"HoleID" character varying(20) ,
"mFrom" Numeric NOT NULL,
"mTo" Numeric NOT NULL,
"mLoss" double precision,
"SpleLength" double precision generated always as
(CASE WHEN "SampleCategory"='NO SAMPLE' THEN "mTo"-"mFrom" WHEN "SampleType"='Standard' or "SampleType"='Blank'
THEN NULL ELSE "mTo"-"mFrom"-"mLoss" END) stored,
"SampleCategory" character varying(10) NOT NULL DEFAULT 'SAMPLE'::character varying,
"SampleCode" character varying(10) NOT NULL DEFAULT '1-Org'::character varying,
"DupSampleID" double precision GENERATED ALWAYS AS (CASE WHEN "SampleCode"='1-Par'
THEN "SampleID"+1 ELSE NULL END) stored,
"StandardCode" character varying(20) GENERATED ALWAYS AS (CASE WHEN "SampleID" LIKE ¨%19'OR THEN Standard'
THEN "SampleCode" ELSE NULL END) stored ,
"DispatchID" character varying(50) NOT NULL DEFAULT 'UNK',
"SampleType" character varying(20) NOT NULL DEFAULT 'Half core'::character varying,
"SampleGen" integer NOT NULL DEFAULT 0,
"kgWeightSampleRef" double precision,
"kgWeightSampleReject" double precision,
"SamplingPlannedBy" character varying(20),
"SampledBy" character varying(5) DEFAULT 'BN'::character varying,
"SamplingDate" date,
"Comments" character varying(255) ,
"EditBy" character varying(5) DEFAULT 'LFC'::character varying,
"EditDate" timestamp with time zone ,
CONSTRAINT "DHSample_Test_pkey" PRIMARY KEY ("SampleID"),
CONSTRAINT "DHSample_Test_EditBy_fkey" FOREIGN KEY ("EditBy")
REFERENCES "RefColStaffName" ("StaffNameCode") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT "DHSample_Test_HoleID_fkey" FOREIGN KEY ("HoleID")
REFERENCES "DHCollar" ("HoleID") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT "DHSample_Test_SampleCategory_fkey" FOREIGN KEY ("SampleCategory")
REFERENCES "RefSpleCateg" ("SampleCategCode") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT "DHSample_Test_SampleCode_fkey" FOREIGN KEY ("SampleCode")
REFERENCES "RefSpleCode" ("SampleCode") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT "DHSample_Test_SampleType_fkey" FOREIGN KEY ("SampleType")
REFERENCES "RefSpleType" ("SampleTypeCode") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT "DHSample_Test_SampledBy_fkey" FOREIGN KEY ("SampledBy")
REFERENCES "RefColStaffName" ("StaffNameCode") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT VALID,
CONSTRAINT "DHSample_Test_mTo_check" CHECK ("mTo" > "mFrom"),
CONSTRAINT "DHSample_Test_mFrom_check" CHECK ("mFrom" >= 0::double precision),
Code 2 : -- Pour vérifier la profondeur saisie afin qu'elle ne dépasse pas celle de la table DHCollar--
CREATE OR REPLACE FUNCTION sple_Depth_Max_CHECKING (hole character varying (20), spledepth double precision)
RETURNS BOOLEAN AS
$$
SELECT CASE
WHEN "FinalDepth" < $2 THEN false
ELSE true
END
FROM "DHCollar" WHERE "HoleID"=$1
$$ LANGUAGE 'sql';
ALTER TABLE "DHSample"
ADD CONSTRAINT sple_max_depth_check CHECK (sple_Depth_Max_CHECKING ("HoleID", "mTo"));
Code 3 :
CREATE OR REPLACE FUNCTION lastupdate()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
New."EditDate":= current_timestamp;
New."EditBy":= 'LFC';
RETURN NEW;
END;
$BODY$;
CREATE TRIGGER last_update
BEFORE INSERT OR UPDATE
ON "DHSample_Test"
FOR EACH ROW
EXECUTE FUNCTION lastupdate();
Code 4 :
CREATE OR REPLACE FUNCTION sple_code_filling()
RETURNS TRIGGER AS
$$
BEGIN
NEW."SampleCategory" :=
CASE WHEN "SampleID" < 0 THEN 'NO SAMPLE'
WHEN (SELECT "SampleID" FROM "DHSample_Test" WHERE CAST("SampleID" AS TEXT)
LIKE ANY ('%31' , '%51' , '%71','%91', '%09', '%29',
'%49', '%69', '%89', '%19', '%39', '59%', '%79', '%99'));
THEN 'QAQC'
ELSE 'SAMPLE'
END;
New."SampleCode" :=
CASE WHEN "SampleType"= 'Half core' AND "SampleID">0 THEN '1-Org'
WHEN "DupSampleID" >0 THEN '1-Par'
WHEN "SampleType" = 'Blank' THEN 'Blk-G'
WHEN "SampleType" = 'Quarter core' AND "DupSampleID"= NULL THEN '2-FDDUP'
WHEN "SampleType" = 'NO SAMPLE' THEN '1-99NR'
ELSE NULL
END;
RETURN NEW;
END
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER fill_sple_tab
BEFORE INSERT OR UPDATE
ON "DHSample_Test"
FOR EACH ROW
EXECUTE FUNCTION sple_code_filling();
Merci
Hors ligne
Le code 2 ne devrait pas exister car, comme le dit la doc, "Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row (see Section 5.4.1)."
Pour le code 4, "bloque" n'est pas très explicite. Ceci étant dit, vu le code trigger, plus la table DHSample_Test est volumineuse, plus le trigger mettra du temps à s'exécuter.
Guillaume.
Hors ligne
Ca n'a pas forcément à voir avec "bloque" mais dans le code 4) il y a une erreur classique de débutant:
WHEN "SampleType" = 'Quarter core' AND "DupSampleID"= NULL
Cette condition sera toujours fausse. Il faudrait utiliser "DupSampleID" IS NULL au lieu de "DupSampleID"=NULL.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Pages : 1
