PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 24/02/2022 16:16:31

Lamethode
Membre

Remplissage automatique

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

#2 24/02/2022 17:50:40

gleu
Administrateur

Re : Remplissage automatique

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

#3 24/02/2022 23:02:48

dverite
Membre

Re : Remplissage automatique

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.

Hors ligne

Pied de page des forums