CREATE OR REPLACE PROCEDURE dbo.sp_UpdateForeignKeys(
    _NameEhrOrTree varchar(100)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Create TEMP TABLE infoFK and populate it with foreign key information
    CREATE TEMP TABLE infoFK (
        foreign_key_name varchar(200) NULL,
        schema_name varchar(200) NULL,
        table_name varchar(200) NULL,
        schema_table_name varchar(200) NULL,
        column_name varchar(200) NULL,
        referenced_table_name varchar(200) NULL,
        referenced_column_name varchar(200) NULL,
        type_ varchar(200) NULL
    );

    -- Populate infoFK with foreign key information based on _NameEhrOrTree
    INSERT INTO infoFK (foreign_key_name, schema_name, table_name, schema_table_name, column_name, referenced_table_name, referenced_column_name, type_)
    SELECT
        c.conname,
        pn.nspname AS schema_name,
        pc2.relname AS table_name,
        conrelid::regclass AS schema_table_name,
        a.attname AS column_name,
        c.confrelid::regclass AS referenced_table_name,
        af.attname AS referenced_column_name,
        substr(te.systemtype,5)
    FROM
        pg_constraint c
        JOIN pg_namespace pn ON c.connamespace = pn.oid
        JOIN pg_class pc2 ON c.conrelid = pc2.oid
        JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
        JOIN pg_attribute af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
        LEFT JOIN vclib.t_entities_attributes te ON te.mappingtable = upper(pc2.relname) AND te.mappingname = a.attname
    WHERE
        contype = 'f'
        AND c.confrelid = _NameEhrOrTree::regclass
        AND pc2.relname NOT LIKE 'f_%';

--     -- Create TEMP TABLE _tab to store SQL text and foreign key names
--     CREATE TEMP TABLE _tab (
--         sqlText text NULL,
--         fk varchar(200) NULL
--     );

    DECLARE
        record_cursor CURSOR FOR
            SELECT schema_table_name, table_name, schema_name, referenced_table_name, referenced_column_name, foreign_key_name, column_name, type_ FROM infoFK;
        _sqldrop text;
        _entname text;
        record record;
    BEGIN
        OPEN record_cursor;
        LOOP
            FETCH record_cursor INTO record;
            EXIT WHEN NOT FOUND;

            execute 'SELECT e.EntName FROM VCLib.T_ENTITIES e JOIN EHR.T_EHR_RUS er on e.EntityID = er.type_ JOIN ' || quote_ident(record.schema_name) || '.' || quote_ident(record.table_name) || ' entity on entity.' || quote_ident(record.column_name) || ' = er.EhrRusID limit 1;'
            into _entname;

            IF record.column_name = 'ehrrusid' THEN
                _sqldrop := 'ALTER TABLE ' || quote_ident(record.schema_name) || '.' || quote_ident(record.table_name) || ' DROP CONSTRAINT IF EXISTS ' || quote_ident(record.foreign_key_name) || ';';
            ELSE
                IF record.type_ IS NOT NULL THEN
                    _sqldrop := 'ALTER TABLE ' || quote_ident(record.schema_name) || '.' || quote_ident(record.table_name) || ' DROP CONSTRAINT IF EXISTS ' || quote_ident(record.foreign_key_name) || ';
                                 ALTER TABLE ' || quote_ident(record.schema_name) || '.' || quote_ident(record.table_name) || ' ADD CONSTRAINT FK_T_' || record.type_ || '_' || record.table_name || '_' || record.column_name || ' FOREIGN KEY(' || quote_ident(record.column_name) || ')
                                 REFERENCES ' || quote_ident(record.referenced_table_name) || ' (' || quote_ident(record.referenced_column_name) || ')';
                ELSE

                    _sqldrop := 'ALTER TABLE ' || quote_ident(record.schema_name) || '.' || quote_ident(record.table_name) || ' DROP CONSTRAINT IF EXISTS ' || quote_ident(record.foreign_key_name) || ';';
                END IF;
            END IF;

            -- Execute the dynamically generated SQL statement
            EXECUTE _sqldrop;
            RAISE NOTICE '%', _sqldrop; -- Optional: Print the executed SQL statement

        END LOOP;

        CLOSE record_cursor;
    END;
END;
$$;