Postgresql – pgBouncer works great but occasionally becomes unavailable

I think this is because you only ever return the first row from the query's result.

The select ... into ... will only retrieve one row and the query select * from result returns only that single record:

You also don't need a PL/pgSQL function, a plain SQL function will work just fine:

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;

Note that the order by inside the CTE is not really useful. You have to sort the final select, not the intermediate steps.

If you do need PL/pgSQL because you are doing more stuff in the function, you should simply change it to:

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;

Our final solution was to split the query so we did pivoting and denormalization in separate steps. This is probably a cleaner solution anyway since it follows the "just do one thing" principle so will probably be easier to maintain.

Thanks for all of the help!