--testing table
CREATE TABLE public.test_patient_table (
                entity_id INTEGER NOT NULL,
                site_held_at INTEGER NOT NULL,
                CONSTRAINT entityid_pk PRIMARY KEY (entity_id)
);

CREATE TABLE public.test_messageq_table (
                entity_id VARCHAR NOT NULL,
                master_id INTEGER NOT NULL,
                message_body VARCHAR NOT NULL,
                CONSTRAINT mq_entity_id_pk PRIMARY KEY (entity_id)
);

CREATE INDEX test_patient_table_siteid_idx
 ON public.test_patient_table
 ( site_held_at );

ALTER TABLE public.test_messageq_table
ADD CONSTRAINT test_patient_table_test_messageq_table_fk
FOREIGN KEY (master_id)
REFERENCES public.test_patient_table (entity_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

--test patient data
insert into test_patient_table values (1, 11111);
insert into test_patient_table values (2, 11111);
insert into test_patient_table values (3, 11111);
insert into test_patient_table values (4, 11111);

insert into test_patient_table values (5, 22222);
insert into test_patient_table values (6, 22222);
insert into test_patient_table values (7, 22222);
insert into test_patient_table values (8, 22222);

insert into test_patient_table values (9, 33333);
insert into test_patient_table values (10, 33333);

insert into test_patient_table values (11, 44444);

--testing message
insert into test_messageq_table values (1, 1, 'aaa');
insert into test_messageq_table values (2, 1, 'aaa');
insert into test_messageq_table values (3, 1, 'aaa');
insert into test_messageq_table values (4, 1, 'aaa');
insert into test_messageq_table values (5, 2, 'aaa');
insert into test_messageq_table values (6, 2, 'aaa');
insert into test_messageq_table values (7, 5, 'aaa');
insert into test_messageq_table values (8, 8, 'aaa');
insert into test_messageq_table values (9, 11, 'aaa');
insert into test_messageq_table values (10, 11, 'bbb');

WITH patient_msg_in_branches AS (
    select distinct test_messageq_table.master_id AS patient_id,
    test_patient_table.site_held_at as site_id
    from test_messageq_table
    inner join test_patient_table
    ON test_messageq_table.master_id = test_patient_table.entity_id
    and site_held_at in (11111,22222) order by patient_id
),
messages_for_patients AS(
    select * from test_messageq_table where master_id in
        (select patient_msg_in_branches.patient_id
            from patient_msg_in_branches)
)select * from messages_for_patients

"1";1;"aaa"
"2";1;"aaa"
"3";1;"aaa"
"4";1;"aaa"
"5";2;"aaa"
"6";2;"aaa"
"7";5;"aaa"
"8";8;"aaa"

drop function getMessageFromSites(text);
CREATE OR REPLACE FUNCTION getMessageFromSites(IN ids TEXT) RETURNS
setof test_messageq_table AS $$
DECLARE
       sites INT[];
       result test_messageq_table%rowtype;

BEGIN
       sites = string_to_array(ids,',');
        raise info 'entire array: %', sites;

WITH patient_msg_in_branches AS (
    select distinct test_messageq_table.master_id AS patient_id,
    test_patient_table.site_held_at as site_id
    from test_messageq_table
    inner join test_patient_table
    ON test_messageq_table.master_id = test_patient_table.entity_id
    and site_held_at = ANY(sites) order by patient_id
),
messages_for_patients AS(
    select * from test_messageq_table where master_id in
        (select patient_msg_in_branches.patient_id
            from patient_msg_in_branches)
)select * into result from messages_for_patients;
return query select * from result;
END;
$$ LANGUAGE plpgsql;

select * from getMessageFromSites('11111,22222');
select * from getMessageFromSites('1')
select * from getMessageFromSites('33333')

"1";1;"aaa"
"2";1;"aaa"
"3";1;"aaa"
"4";1;"aaa"
"5";2;"aaa"
"6";2;"aaa"
"9";11;"aaa"
"10";11;"bbb"

CREATE OR REPLACE FUNCTION getMessageFromSites(IN ids TEXT) RETURNS
setof test_messageq_table AS $$
DECLARE
       sites INT[];
       result test_messageq_table%rowtype;

BEGIN
       sites = string_to_array(ids,',');
       raise info 'entire array: %', sites;
 return QUERY

    WITH patient_msg_in_branches AS (
        select distinct test_messageq_table.master_id AS patient_id,
        test_patient_table.site_held_at as site_id
        from test_messageq_table
        inner join test_patient_table
        ON test_messageq_table.master_id = test_patient_table.entity_id
        and site_held_at = ANY(sites) order by patient_id
    ),
    messages_for_patients AS(
        select * from test_messageq_table where master_id in
            (select patient_msg_in_branches.patient_id
                from patient_msg_in_branches)
    )
    select * from messages_for_patients;

END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION getMessageFromSites2(ids TEXT) RETURNS
   setof test_messageq_table
AS
$$
  WITH patient_msg_in_branches AS (
      select distinct test_messageq_table.master_id AS patient_id,
             test_patient_table.site_held_at as site_id
      from test_messageq_table
      join test_patient_table ON test_messageq_table.master_id = test_patient_table.entity_id
                                and site_held_at = ANY (string_to_array($1,',')::int[])
  ),
  messages_for_patients AS
  (
    select *
    from test_messageq_table
    where master_id in (select patient_msg_in_branches.patient_id
                        from patient_msg_in_branches)
  )
  select *
  from messages_for_patients;
$$
LANGUAGE sql;

select * from getMessageFromSites('11111,44444');
select * from getMessageFromSites('22222');
select * from getMessageFromSites('1')
select * from getMessageFromSites('33333')

select * from getMessageFromSites2('11111');
select * from getMessageFromSites2('22222');
select * from getMessageFromSites2('33333');
select * from getMessageFromSites('44444,11111');
select * from getMessageFromSites('1');

CREATE OR REPLACE FUNCTION getMessageFromSites(ids TEXT) RETURNS
   setof test_messageq_table
AS
$$
  WITH patient_msg_in_branches AS (
      select distinct test_messageq_table.master_id AS patient_id,
             test_patient_table.site_held_at as site_id
      from test_messageq_table
         join test_patient_table ON test_messageq_table.master_id = test_patient_table.entity_id
                                and site_held_at = ANY (string_to_array(ids,',')::int[])
  ),
  messages_for_patients AS
  (
    select *
    from test_messageq_table
    where master_id in (select patient_msg_in_branches.patient_id
                        from patient_msg_in_branches)
  )
  select *
  from messages_for_patients;
$$
LANGUAGE sql;

begin
  ....
  return query
    WITH patient_msg_in_branches AS (
        select distinct test_messageq_table.master_id AS patient_id,
        test_patient_table.site_held_at as site_id
        from test_messageq_table
        inner join test_patient_table
        ON test_messageq_table.master_id = test_patient_table.entity_id
        and site_held_at = ANY(sites) order by patient_id
    ),
    messages_for_patients AS(
        select * from test_messageq_table where master_id in
            (select patient_msg_in_branches.patient_id
                from patient_msg_in_branches)
    )
    select * from messages_for_patients;
end;

CREATE TABLE patient (
   patient_id   int PRIMARY KEY
 , site_held_at int NOT NULL
);

CREATE TABLE messageq (
   messageq_id  varchar PRIMARY KEY  -- varchar ?!
 , patient_id   int NOT NULL REFERENCES patient
 , message_body varchar NOT NULL
);

CREATE INDEX patient_site_idx ON patient(site_held_at);
CREATE INDEX messageq_patient_id_idx ON patient(patient_id); -- !!

INSERT INTO patient VALUES
  (1, 11111)
, (2, 11111)
, (3, 11111)
, (4, 11111)
, (5, 22222)
, (6, 22222)
, (7, 22222)
, (8, 22222)
, (9, 33333)
, (10, 33333)
, (11, 44444);

INSERT INTO messageq VALUES
  ('m1', 1, 'aaa1')
, ('m2', 1, 'aaa2')
, ('m3', 1, 'aaa3')
, ('m4', 1, 'aaa4')
, ('m5', 2, 'aaa5')
, ('m6', 2, 'aaa6')
, ('m7', 5, 'aaa7')
, ('m8', 8, 'aaa8')
, ('m9', 11, 'aaa9')
, ('m10', 11, 'bbb10');

CREATE OR REPLACE FUNCTION f_get_msg_from_sites(VARIADIC _id int[])
  RETURNS SETOF messageq AS
$func$
   SELECT m.*
   FROM   patient  p
   JOIN   messageq m USING (patient_id)
   WHERE  p.site_held_at = ANY($1)
$func$ LANGUAGE sql;

SELECT * FROM f_get_msg_from_sites(11111, 44444);
SELECT * FROM f_get_msg_from_sites(22222);