CREATE OR REPLACE FUNCTION fnc_load_tab_from_xml(pschema character varying, ptablename character varying, pdate xml, poracle smallint DEFAULT 0, pidxsplit smallint DEFAULT 0)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE 
  vRec    record;
  xmlCol  TEXT; 
  selCol  TEXT;
  insCol  TEXT; 
  xmlSep  TEXT; 
  varsql  TEXT;
  vSchema varchar;
BEGIN
  -- modificarile trebuie facute in tandem fnc_load_tab_from_xml <-> prc_load_tab_from_xml
  IF COALESCE(Length(pschema), 0) = 0 THEN -- tabela temporara
    SELECT Max(n.nspname) INTO vSchema
    FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'r' 
      AND c.relname = Lower(ptablename)
      AND n.nspname like 'pg_temp_%'
      AND pg_catalog.pg_table_is_visible(c.oid);
  ELSE  
    vSchema := pschema;
  END IF;  

  IF pIdxSplit = 0 THEN 
    EXECUTE 'TRUNCATE TABLE '||vSchema||'.'||pTableName;
  END IF;

  xmlSep := '
          ';
        
  FOR vRec IN (
    SELECT
      ORDINAL_POSITION,
      '"'||column_name||'"' insCol,
      Concat(
        case
          when poracle = 1 and DATA_TYPE = 'timestamp without time zone'   then 'TO_TIMESTAMP(Substr(xtab."'||column_name||'",1,19),''dd-mm-yyyy hh24:mi:ss'') '  
          when poracle = 1 and DATA_TYPE = 'numeric' and NUMERIC_SCALE > 0 then 'Cast(replace(xtab."'||column_name||'",'','',''.'') AS numeric('||NUMERIC_PRECISION||','||NUMERIC_SCALE||')) '
          when poracle = 1 and DATA_TYPE = 'bytea'                         THEN 'decode(xtab."'||column_name||'", ''hex'') '         
          when                 DATA_TYPE = 'bytea'                         THEN 'decode(xtab."'||column_name||'", ''base64'') '         
          else 'xtab.'
        end,  
        '"'||column_name||'"'  
      ) selCol,
      CONCAT(
        '"'||column_name||'"',
        ' ',
        case 
          when poracle = 1 and DATA_TYPE = 'timestamp without time zone'    then 'character varying'
          when poracle = 1 and DATA_TYPE = 'numeric' and NUMERIC_SCALE > 0  then 'character varying'
          when                 DATA_TYPE = 'bytea'                          THEN 'text'         
          else DATA_TYPE
        end,
        case
          when poracle = 1 and DATA_TYPE = 'timestamp without time zone'    then '(40)'
          when poracle = 1 and DATA_TYPE = 'numeric' and NUMERIC_SCALE > 0  then '('||NUMERIC_PRECISION+NUMERIC_SCALE+1||')'
          when                 DATA_TYPE = 'bytea'                          THEN ''         
          when DATA_TYPE = 'character varying'                              then '('||CHARACTER_MAXIMUM_LENGTH||')'
          when NUMERIC_PRECISION is not null                                then '('||NUMERIC_PRECISION||','||NUMERIC_SCALE||')'
        end,
        ' PATH ''',
        case when poracle = 1 then UPPER(column_name) else column_name end,
        ''''
      ) xmlCol,
      case when ORDINAL_POSITION < Max(ORDINAL_POSITION) over (partition by TABLE_SCHEMA, TABLE_NAME) then ',' end sepCol
    FROM information_schema.columns
    WHERE TABLE_SCHEMA = vSchema AND TABLE_NAME = Lower(ptablename)
    ORDER BY ORDINAL_POSITION 
  ) LOOP
    insCol := Concat(insCol,         vRec.insCol, vRec.sepCol);
    selCol := Concat(selCol,         vRec.selCol, vRec.sepCol);
    xmlCol := Concat(xmlCol, xmlSep, vRec.xmlcol, vRec.sepCol);
  END LOOP;

  varSQL := '
    INSERT INTO '||vSchema||'.'||pTableName||'('||insCol||')
    SELECT '||selCol||'
    FROM
      tmp_xmldata xtext,
      XMLTABLE (''//'||case when poracle = 1 then 'ROW' else 'row' end||''' PASSING xtext.xml_text
        COLUMNS'||XMLCol||'
      ) xtab;';

  CREATE TEMPORARY TABLE if not EXISTS tmp_xmldata (xml_text xml) on commit drop;
  INSERT INTO tmp_xmldata (xml_text) VALUES (pdate);

  EXECUTE varSQL;
--EXCEPTION nu merge si in prc_load_tab_from_xml pentru ca am au aparut erori la copiere date din Oracle in PG 
--fara bucatita de EXCEPTION nu apar acele erori
EXCEPTION
  WHEN OTHERS THEN
    CREATE TABLE if not EXISTS public.debug_xmldata (
      NumeSchema VARCHAR(100), 
      NumeTabla  VARCHAR(100),
      xml_text   XML,
      Sql_Text   TEXT,  
      sql_time   TIMESTAMP(6) 
    );
    INSERT INTO public.debug_xmldata (NumeSchema, NumeTabla, xml_text, Sql_Text, sql_time) VALUES ( pschema, ptablename, pdate, varSQL, clock_timestamp());
    RAISE;
END;
$function$
