-- FUNCTION: public.parse_french_date(text)

-- DROP FUNCTION IF EXISTS public.parse_french_date(text);

CREATE OR REPLACE FUNCTION public.parse_french_date(
	dt_text text)
    RETURNS date
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    clean_text text;
BEGIN
    clean_text := dt_text;

    -- If weekday is present (3 letters + space), remove it
    IF substring(clean_text from 1 for 3) IN ('Lun','Mar','Mer','Jeu','Ven','Sam','Dim') THEN
        clean_text := substring(clean_text from 5);
    END IF;

    -- Replace French months with English
    clean_text := replace(clean_text, 'Janv.', 'Jan');
    clean_text := replace(clean_text, 'Févr.', 'Feb');
    clean_text := replace(clean_text, 'Mars',  'Mar');
    clean_text := replace(clean_text, 'Avr.',  'Apr');
    clean_text := replace(clean_text, 'Mai',   'May');
    clean_text := replace(clean_text, 'Juin',  'Jun');
    clean_text := replace(clean_text, 'Juil.', 'Jul');
    clean_text := replace(clean_text, 'Août',  'Aug');
    clean_text := replace(clean_text, 'Sept.', 'Sep');
    clean_text := replace(clean_text, 'Oct.',  'Oct');
    clean_text := replace(clean_text, 'Nov.',  'Nov');
    clean_text := replace(clean_text, 'Déc.',  'Dec');

    RETURN to_timestamp(clean_text, 'DD Mon YYYY HH24:MI:SS')::date;
END;
$BODY$__@@__

ALTER FUNCTION public.parse_french_date(text)
    OWNER TO postgres__@@__

UPDATE ardoc.tb_consultation SET dconsultation=parse_french_date(dateconsultation) WHERE dconsultation IS NULL__@@__
UPDATE starged.tb_consultation SET dconsultation=parse_french_date(dateconsultation) WHERE dconsultation IS NULL__@@__