CREATE or Replace Function sp_EhrRefUpdate("IdRefB" uuid)
As $$
BEGIN
IF "IdRefB" IS NULL THEN

DROP TABLE IF EXISTS TEMP_TABLE;
CREATE TEMP TABLE TEMP_TABLE(
    Entities int
  , AttributeName varchar
  , EhrEntity int
);

INSERT INTO TEMP_TABLE(
    Entities
  , AttributeName
  , EhrEntity
)
SELECT
  atr.EntityID, 
  atr.Name, 
  entRef.EntityID 
FROM
  VCLib.T_ENTITIES_ATTRIBUTES atr 
    JOIN VCLib.T_ENTITIES entRef ON (entRef.Schema || '.' || entRef.EntName) = atr.SystemType
WHERE
    entRef.Schema = 'EHR'
    AND NOT EXISTS(
            SELECT 1
            FROM EHR.T_EHR_REFERENCE ref
            WHERE
                ref.Entities = atr.EntityID
                AND ref.AttributeName = atr.Name
                AND ref.EhrEntity = entRef.EntityID);

WITH curVers (Entities, Rb) AS (
  SELECT
    rb.Entities, 
    rb.EhrRusRefBooksID 
  FROM
    EHR.T_EHR_RUS_REF_BOOKS rb 
  WHERE
      rb.CurrVersion IS NOT NULL
    AND EXISTS(
        SELECT 1
        FROM TEMP_TABLE t
        WHERE
            t.EhrEntity = rb.Entities)
    AND NOT EXISTS(
            SELECT 1
            FROM EHR.T_EHR_RUS_REF_BOOKS rbb
            WHERE
                rbb.Entities = rb.Entities
                AND (CAST(SUBSTRING(REPLACE(rbb.CurrVersion, ',', '.'),1,POSITION('.' IN REPLACE(rbb.CurrVersion, ',', '.')) -1) AS DECIMAL(31, 10))
                    > CAST(SUBSTRING(REPLACE(rb.CurrVersion, ',', '.'),1,POSITION('.' IN REPLACE(rb.CurrVersion, ',', '.')) -1) AS DECIMAL(31, 10))
                OR (CAST(SUBSTRING(REPLACE(rbb.CurrVersion, ',', '.'),1,POSITION('.' IN REPLACE(rbb.CurrVersion, ',', '.')) -1) AS DECIMAL(31, 10))
                    = CAST(SUBSTRING(REPLACE(rb.CurrVersion, ',', '.'),1,POSITION('.' IN REPLACE(rb.CurrVersion, ',', '.')) -1) AS DECIMAL(31, 10))
                AND CAST(SUBSTRING(REPLACE(rbb.CurrVersion, ',', '.'),POSITION('.' IN REPLACE(rbb.CurrVersion, ',', '.')) + 1,LENGTH(REPLACE(rbb.CurrVersion, ',', '.')) - POSITION('.' IN REPLACE(rbb.CurrVersion, ',', '.'))) AS DECIMAL(31, 10))
                    > CAST(SUBSTRING(REPLACE(rb.CurrVersion, ',', '.'),POSITION('.' IN REPLACE(rb.CurrVersion, ',', '.')) + 1,LENGTH(REPLACE(rb.CurrVersion, ',', '.')) - POSITION('.' IN REPLACE(rb.CurrVersion, ',', '.'))) AS DECIMAL(31, 10)))
                    )))

INSERT INTO EHR.T_EHR_REFERENCE
SELECT
    uuid_generate_v4()
  , tab.Entities
  , tab.AttributeName
  , cv.Rb
  , tab.EhrEntity
FROM TEMP_TABLE tab
  JOIN curVers cv ON cv.Entities = tab.EhrEntity
WHERE
    cv.Rb IS NOT NULL;

DROP TABLE TEMP_TABLE;

ELSE

UPDATE ref
SET
    ref.EhrRusRefBooks = "IdRefB"
FROM EHR.T_EHR_REFERENCE ref
WHERE EXISTS(
        SELECT 1
        FROM EHR.T_EHR_RUS_REF_BOOKS refbook
        WHERE refbook.EhrRusRefBooksID = "IdRefB"
          AND ref.EhrEntity = refbook.Entities
          AND ref.EhrRusRefBooks IS NULL);

INSERT INTO EHR.T_EHR_REFERENCE (
    EhrReferenceID
  , Entities
  , AttributeName
  , EhrRusRefBooks
  , EhrEntity
) 
SELECT
    uuid_generate_v4()
  , ea.EntityID
  , ea.Name
  , "IdRefB"
  , eEhr.EntityID
FROM VCLib.T_ENTITIES_ATTRIBUTES ea
    LEFT JOIN VCLib.T_ENTITIES eEhr ON eEhr.Schema || eEhr.EntName = ea.SystemType
    LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS refbook ON refbook.EhrRusRefBooksID = "IdRefB"
WHERE
    eEhr.EntityID = refbook.Entities
    AND NOT EXISTS(
            SELECT 1
            FROM EHR.T_EHR_REFERENCE err
            WHERE
                err.EhrEntity = eEhr.EntityID
                AND err.Entities = ea.EntityID
                AND err.AttributeName = ea.Name);
End IF;
END $$
Language plpgsql;
