CREATE OR REPLACE FUNCTION public.gmi_richiedente_data_retention_policy(
        idrichiedente integer)
    RETURNS INTEGER
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE 
    AS $BODY$
        DECLARE tot INTEGER := 0;
        DECLARE rcnt INTEGER := 0;
        DECLARE temprow RECORD;
        BEGIN 
        FOR temprow IN 
        SELECT
        kcu.table_name,
        kcu.column_name,
        kcu.table_name || '.' || kcu.column_name AS fkkey
          FROM information_schema.table_constraints tc
        INNER JOIN information_schema.constraint_table_usage tcu on tcu.constraint_name = tc.constraint_name  
        --INNER JOIN information_schema.constraint_column_usage ccu on ccu.constraint_name = tcu.constraint_name
        INNER JOIN information_schema.key_column_usage kcu on kcu.constraint_name = tc.constraint_name 
        WHERE tcu.table_name ='richiedente'
        AND tc.constraint_type = 'FOREIGN KEY'
        LOOP 
               EXECUTE FORMAT('(SELECT count(*) as cnt FROM %s WHERE %s = %s)', temprow.table_name, temprow.fkkey, idrichiedente) INTO rcnt;
            tot := tot + rcnt;
        END LOOP;
        RETURN tot;
        END;$BODY$;
    
    ALTER FUNCTION public.gmi_richiedente_data_retention_policy(integer)
        OWNER TO sa;