DROP FUNCTION IF EXISTS public.fn_getcommentdrugrequestlist;
DROP FUNCTION IF EXISTS dbo.fn_getcommentdrugrequestlist;
DROP FUNCTION IF EXISTS rds.fn_getcommentdrugrequestlist;

CREATE OR REPLACE FUNCTION rds.fn_getcommentdrugrequestlist(
    id uuid
) RETURNS uuid
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_id uuid := id;
BEGIN
    
    RETURN
        (SELECT (SELECT drl.drugapplicationlistitemid
                 FROM rds.t_drug_application_list_item AS drl
                 JOIN rds.t_drug_application_list da ON da.drugapplicationlistid = drl.drugapplicationlist
                 WHERE drl.requestcode = dd.requestcode
                   AND da.begindate <= dr.beginperiod
                   AND da.enddate >= dr.endperiod
                   AND (dr.requesttype = drl.benefittype)
                 LIMIT 1
                )
         FROM rds.t_drug_request_comments AS dd
         JOIN rds.t_drug_request AS dr ON dd.drugrequest = dr.drugrequestid
         WHERE dd.drugrequestcommentsid = p_id
        );
END;
$$