Forums PostgreSQL.fr

COALESCE

ALTER TABLE tableA  ADD CONSTRAINT la_table_pkey PRIMARY KEY (champ1, champ2,champ3);

alter TABLE tableB add constraint cle_etrangere foreign key (colonneB) references tableA (id_rsa) on delete cascade;

NOTICE:  constraint cle_etrangère_diag on table diagnostique depends on table rsa
NOTICE:  constraint cle_etrangère_um on table unite_medicale depends on table rsa
NOTICE:  constraint cle_etrangère on table acte depends on table rsa

ERROR: cannot drop table rsa because other objects depend on it
État SQL :2BP01
Astuce : Use DROP ... CASCADE to drop the dependent objects too.

ERROR: syntax error at or near "importer_um"
État SQL :42601
Contexte : SQL statement in PL/PgSQL function "importer_pmsi" near line 24

CREATE OR REPLACE FUNCTION importer_rsa (donnee text ) RETURNS void AS
    $BODY$
    DECLARE

    a character varying(9);  b character varying(3);  c character varying(10);  d character varying(3); e character varying(3); f character varying(2) ;  g character varying(2) ; h character varying(1);
    i character varying(2) ; j character varying(1) ; k character varying(3) ; l character varying(2) ; m character varying(2) ; n character varying(1) ;  o character varying(2);  p character varying(1) ;
    q character varying(3);  r character varying(2) ; s character varying(3) ;  t character varying(3) ; u character varying(1) ;  v character varying(1) ;  w character varying(1) ;  x character varying(2) ;
    y character varying(4) ; z character varying(1) ; ab character varying(1) ;  ac character varying(1);  ad character varying(4) ;ae character varying(5) ;af character varying(4) ;ag character varying(2) ; 
    ah character varying(3) ; ai character varying(4) ; aj character varying(4) ; ak character varying(1) ; al character varying(4) ; am character varying(3) ; an character varying(3) ; ao character varying(3) ;
    ap character varying(3) ; aq character varying(2) ;  ar character varying(3) ; as character varying(3) ;at character varying(3) ;au character varying(3);   av character varying(1) ; aw character varying(3) ;
    ax character varying(3); ay character varying(3) ; az character varying(3) ; a1 character varying(3); a2 character varying(3); a3 character varying(3) ;  a4 character varying(3);  a5 character varying(3) ;
    a6 character varying(1) ; a7 character varying(6) ; a8 character varying(6) ; a9 character varying(2) ; a10 character varying(4) ; 
   
     
begin
    a:=substring (donnee from 1 for 9);  b:= substring (donnee from 10 for 3);  c:= substring (donnee from 13 for 10); d:= substring (donnee from 23 for 3); e:= substring (donnee from 26 for 3);  f:= substring (donnee from 29 for 2); g:= substring (donnee from 31 for 2);
    h:= substring (donnee from 33 for 1); i:= substring (donnee from 34 for 2); j:= substring (donnee from 36 for 1); k:= substring (donnee from 37 for 3); l:= substring (donnee from 40 for 2);  m:= substring (donnee from 42 for 2); n:= substring (donnee from 44 for 1);
    o:= substring (donnee from 45 for 2); p:= substring (donnee from 47 for 1); q:= substring (donnee from 48 for 3);  r:= substring (donnee from 51 for 2); s:= substring (donnee from 53 for 3);  t:= substring (donnee from 56 for 3);
    u:= substring (donnee from 59 for 1);
    v:= substring (donnee  from 60 for 1); W:= substring (donnee from 61 for 1); x:= substring (donnee from 62 for 2);  y:= substring (donnee from 64 for 4); z:= substring (donnee from 68  for 1);  ab:= substring (donnee from 69 for 1); ac:= substring (donnee  from 70  for 1);
    ad:= substring(donnee  from 71 for 4); ae:= substring(donnee from 75 for 5); af:= substring(donnee from 80 for 4); ag:=substring (donnee from 84 for 2); ah:= substring(donnee from 86 for 3);   ai:= substring(donnee from 89 for 4);
    aj:= substring (donnee from 93  for 4); ak:= substring(donnee from 97 for 1); al:= substring(donnee from 98 for 4); am:= substring(donnee from 102 for 3); an:= substring(donnee from 105 for 3);ao:= substring(donnee from 108 for 3);
    ap:= substring(donnee from  111 for 3); aq:= substring (donnee from 114 for 2);
    ar:= substring(donnee from 116 for 3);as:= substring(donnee from 119 for 3); at:= substring(donnee from 122 for 3); au:= substring(donnee from 125 for 3);av:= substring(donnee from 128 for 1); aw:= substring(donnee from 129 for 3); ax:= substring (donnee from 132 for 3);
    ay:= substring(donnee from 135 for 3); az:= substring(donnee from 138 for 3);a1:= substring(donnee from 141 for 3); a2:= substring(donnee from 144 for 3); a3:= substring(donnee  from 147 for 3); a4:= substring(donnee from 150 for 3); a5:= substring (donnee from 153 for 3);
    a6:= substring(donnee from 156 for 1); a7:= substring(donnee from 157 for 6); a8:= substring(donnee from 163 for 6); a9:= substring(donnee from 169 for 2); a10:=substring(donnee from 171 for 4);
   

INSERT INTO rsa (num_finess, num_vers_rsa, num_index_rsa , num_vers_rssgpe ,num_vers_genrsa, Gpgelu_vers_classif, gpgelu_ghm_Cmd, Gpgelu_ghm_Type ,gpgelu_ghm_Num, gpgelu_ghm_Cplexite,
                    Gpgelu_code_retour ,Gpgegenrsa_vers_classif ,Gpgegenrsa_ghm_Cmd,gpgegenrsa_ghm_Type,gpgegenrsa_ghm_num , gpgegenrsa_ghm_Cplexite, Gpgegenrsa_code_retour,
                    nb_rum_rss_orig ,age_en_annee ,age_en_jour, Sexe ,mode_entree_pmsimco ,Provenance ,mois_sortie ,annee_sortie ,mode_sortie_pmsimco, Destination, type_sej,
                    duree_total_sej_pmsi,code_geog_res,poids_entree,nb_seance ,Igs2 ,  num_ghs_ghmgenrsa,
                    nb_jne_sup_bor_extr_haute , sej_inf_bor_extr_basse ,
                    forfait_dialyse, nb_sup_hem_hseance ,
                    nb_sup_entr_dia_per_auto_hseance,
                    nb_sup_entr_dia_per_cont_amb_hseance, nb_sup_entr_hem_hseance ,
                    nb_seance_avt_sros,
                    nb_acte_men_ghm24z05z , nb_acte_men_ghm24z06z ,                                           
                    nb_acte_men_ghm24z07z,
                    nb_sup_caisson_hyperbare ,type_prest_prelv_org , nb_sup_sra_rean , nb_sup_rea_rean ,
                    nb_sup_soin_int_prov_rean , nb_sup_stf , nb_sup_ssc , nb_sup_src,nb_sup_nn1 ,nb_sup_nn2 ,nb_sup_nn3,Pge_lit_dedie_soin_pal ,Dp , Dr, nb_diag_ass_sig_rsa, nb_zone_acte_rsa )
                   
                                                   values (a, b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,ab,ac,ad,ae,af,ag,ah,
                                                   ai,aj,ak,al,am,an,ao, ap,aq,ar,as,at,au,av,aw,ax,ay,az,a1,a2,a3,a4,a5,a6,a7,a8,a9,a10);   
   return;
                                               
END;
$BODY$
LANGUAGE plpgsql ;

create or replace function importer_um (donnee text) returns void as      --fonction qui importe 1 um dans la table unité_médicale
$BODY$
declare
   -- declaration des variables
   a character varying(2);
   b character varying(3);
   c character varying(1);
   d character varying(1);
   e character varying(2);

begin
   -- soustraction des sous chaines et leur affectation aux variables
   a:= substring (donnee_um from 1 for 2);
   b:= substring (donnee_um from 3 for 3);
   c:= substring (donnee_um from 6 for 1);
   d:= substring (donnee_um from 7 for 1);
   e:= substring (donnee_um from 8 for 2);
   insert into unite_medicale (type_um , dur_sej_part , val_rea , val_part, position_dp )  values (a, b, c, d, e);

END;
$BODY$
LANGUAGE plpgsql ;

create or replace function importer_diag (donnee_diag text ) returns void as --fonction qui importe 1 um dans la table unité_médicale
$BODY$
declare  -- declaration des variables
     liste varchar (6); 
begin
    -- soustraction des sous chaines et leur affectation aux variables
    liste:=substring(donnee_diag from 1 for 6 );

  insert into diagnostique (liste_code_diagnostique )  values (liste);
                                                                                                                                                             
return;                     
END;
$BODY$
LANGUAGE plpgsql ;

create or replace function importer_acte (donnee text) returns void as   --fonction qui importe 1 acte dans la table acte
$BODY$
declare -- declaration des variables
   a character varying(3);
   b character varying(7);
   c character varying(1) ;
   d character varying(1) ;
   e character varying(1);
   f character varying(4);
   g character varying(1);
   h character varying(1);
   i character varying(2);

begin
    -- soustraction des sous chaines et leur affectation aux variables
   a:= substring (donnee_acte from 1 for 3);
   b:= substring (donnee_acte from 4 for 7);
   c:= substring (donnee_acte from 11 for 1);
   d:= substring (donnee_acte from 12 for 1);
   e:= substring (donnee_acte from 13 for 1);
   f:= substring (donnee_acte from 14 for 4);
   g:= substring (donnee_acte from 18 for 1);
   h:= substring (donnee_acte from 19 for 1);
   i:= substring (donnee_acte from 20 for 2);

   insert into acte (delai_dep_dat_entree ,code_ccam , phase , activite, exten_doc,modificateur,remb_excep,ass_non_prevue,nb_exe_acte )  values (a, b, c, d, e,f,g,h,i);
return;
END;
$BODY$
LANGUAGE plpgsql ;

create or replace function  importer_pmsi (donnee_pmsi text ) returns varchar as
$BODY$
declare
    bloc_rsa character varying(174);    --données du rsa
    bloc_um character varying(891);     --données de l'ensemble des um
    bloc_diag character varying(594);   --données de l'ensemble des diagnostics
    bloc_acte character varying(209979);   --données de l'ensemble des actes
    nb_um Integer;   --nombre de um
    nb_diag Integer;  --nombre de diagnostics
    nb_acte Integer;  --nombre d'actes
    temp character varying(209979);      --variable temporaire
begin
   bloc_rsa:=substring(donnee_pmsi from 1 for 174);
   nb_um := CAST(substring(donnee_pmsi from 51 for 2) AS Integer);   --c'est le nombre de UM
   nb_diag := CAST(substring(donnee_pmsi from 169 for 2) AS Integer); -- c'est la nombre de diagnostic
   nb_acte := CAST(substring(donnee_pmsi from 171 for 4) AS Integer); --c'est la nombre d'actes

   bloc_um:=substring(donnee_pmsi from 175 for num_um*9);             --tous les blocs de données des um
   bloc_diag:=substring(donnee_pmsi from (175+num_um*9) for num_diag*6); --tous les blocs de  données  des diagnostics
   bloc_acte:=substring(donnee_pmsi from (175+num_um*9+num_diag*6) for num_acte*21); --tous les blocs de données des actes

   PERFORM importer_rsa(bloc_rsa);
   
   for l in 0..nb_um loop                           
     temp:= substring(bloc_um from (l*9+1) for 9)   
     importer_um(temp);
    end loop ; 

   for l in 0..nb_diag loop                         
       temp:= substring(bloc_diag from (l*6+1) for 6)   
      select importer_diag (temp);
   end loop ;                   

   for l in 0..nb_acte loop                         
       temp:= substring(bloc_acte from (l*21+1) for 21) 
       importer_acte(temp);                       
   end loop ;
  return varchar;

END;

$BODY$
LANGUAGE plpgsql ;

select importer_pmsi('01000840721300000000171110021024C30Z0001024C30Z00001033   28 0820068  00018440000000000080290000099990000000000000000000000000000000000000000000000000000000O039        000003  00123DP001JNJD00204      001001JNJD00201      001001DEQP00701      001');