CREATE OR REPLACE FUNCTION "public"."soundexfr" (text) RETURNS text AS
$body$
/*
   Fonction SOUNDEX francisé
   
   Exemple : SELECT soundexfr('durand'),soundexfr('durhand'),soundexfr('durond'),soundexfr('dupond');
             D653   D653   D653   D153
*/
DECLARE
st text;
st2 text;
i  int2;
ch char;
BEGIN
 st2:='';

 -- on transforme les voyelles et on passe en majuscule
 st:=translate($1,'aàäâeéèêëiïîoôöuùûücç','AAAAEEEEEIIIOOOUUUUCC');
 st:=upper(st);
 
 -- on enleve les espaces
 FOR i IN 1..length(st) LOOP
  IF substring(st,i,1)<>' ' THEN
   st2:=st2||substring(st,i,1);
  END IF;
 END LOOP;

 st:='';

 FOR i IN 1..length(st2) LOOP
  IF NOT (substring(st2,i,1) IN ('A','E','I','O','U','Y','H','W')) THEN
   st:=st||substring(st2,i,1);
  END IF;
 END LOOP;

 st2:=substring(st,1,1);

 FOR i IN 2..length(st) LOOP
  ch:=substring(st,i,1);
  IF ch IN ('B','P') THEN st2:=st2||'1';
   ELSIF ch IN ('C','K','Q') THEN st2:=st2||'2';
    ELSIF ch IN ('D','T') THEN st2:=st2||'3';
     ELSIF ch='L' THEN st2:=st2||'4';
      ELSIF ch IN ('M','N') THEN st2:=st2||'5';
       ELSIF ch='R' THEN st2:=st2||'6';
        ELSIF ch IN ('G','J') THEN st2:=st2||'7';
         ELSIF ch IN ('S','X','Z') THEN st2:=st2||'8';
          ELSIF ch IN ('F','V') THEN st2:=st2||'9';
           END IF;
 END LOOP;
 
 st:=substring(st2,1,1);

 FOR i IN 1..length(st2) LOOP
  IF substring(st2,i,1) <> substring(st,length(st),1) THEN
   st:=st||substring(st2,i,1);
  END IF;
 END LOOP;
 
 IF length(st)<4 THEN
  FOR i IN length(st)..4 LOOP
   st:=st||'0';
  END LOOP;
 ELSIF length(st)>4 THEN
  st:=substring(st,1,4);
 END IF;
 
 return st;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION "public"."encode64" (chaine text) RETURNS text AS
$body$
/*
    Encodage en base 64
    
    Exemple : SELECT encode64('Salut');
              Ks5iTNG
*/
DECLARE
 i integer;
 a integer = 0;
 x integer;
 b integer = 0;
 resultat text = '';
 codes64 text = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz+/';
BEGIN
  FOR i IN 1..length(chaine) LOOP
   x:=ascii(substring(chaine from i for 1));
   b:=b*256+x;
   a:=a+8;
   WHILE a>=6 LOOP
    a:=a-6;
    x:=b/(1<<a);
    b:=b%(1<<a);
    resultat:=resultat||substring(codes64 from x+1 for 1);
   END LOOP;
  END LOOP;

  IF a>0 THEN
   x:=b<<(6-a);
   resultat:=resultat||substring(codes64 from x+1 for 1);
  END IF;
  
  return resultat;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION "public"."decode64" (chaine text) RETURNS text AS
$body$
/*
   Decodage en base 64
   
   Exemple : SELECT decode64('Ks5iTNG');
             Salut
*/
DECLARE
 i integer;
 a integer = 0;
 x integer;
 b integer = 0;
 codes64 text = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz+/';
 resultat text = '';
BEGIN
 FOR i IN 1..length(chaine) LOOP
  x:=position(substring(chaine from i for 1) in codes64)-1;
  IF x>=0 THEN
   b:=b*64+x;
   a:=a+6;
   IF a>=8 THEN
    a:=a-8;
    x:=b>>a;
    b:=b%(1<<a);
    x:=x%256;
    resultat:=resultat||chr(x);
   END IF;
  ELSE
   Exit;
  END IF;
 END LOOP;
 return resultat;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION "public"."inverserchaine" (chaine text) RETURNS text AS
$body$
/*
   Inverse la chaine

  Exempe : SELECT inverserchaine('postgresql');
           lqsergtsop
*/
DECLARE
 i integer;
 resultat text = '';
BEGIN
 FOR i IN REVERSE length(chaine)..1 LOOP
  resultat:=resultat||substring(chaine from i for 1);
 END LOOP;
 return resultat;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION "public"."minimizetext" (text) RETURNS text AS
$body$
/*
 Fonction minimizetext
 Permet de simplifier une phrase au maximum
  -> Suppression des lettres en doubles
  -> Conversion des accents

 Exemple : SELECT minimizetext('hellllooooo    monnnnndeeee');
           helomonde
*/
DECLARE
 chaine text;
 desti text;
 last char(1);
 current char(1);
 i integer;
BEGIN
 chaine:=translate(lower($1),'àâéèêëîïìôòùû','aaeeeeiiioouu');
 chaine:=replace(chaine,'-','');
 chaine:=replace(chaine,' ','');
 chaine:=trim(chaine);
 last:=' ';
 desti:='';
 FOR i IN 1..length(chaine) LOOP
  current:=substring(chaine,i,1);
  IF current<>last THEN
   last:=current;
   desti:=desti||current;
  END IF;
 END LOOP;
 RETURN desti;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION "public"."stroccurence" (chaine text, souschaine text) RETURNS integer AS
$body$
/* Compte le nombre d'occurence d'une souschaine dans une chaine
   
   Exemple : SELECT stroccurence('Ici un mot, et la un deuxieme mot','mot');
             2
 */
BEGIN
 return array_upper(string_to_array(chaine,souschaine),1)-1;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;