7
--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');    

When I tried to find all messages from message table in site I am interested, I wrote a CTE and it works fine, let's say I am interested in site 11111 and 22222:

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

The result is as expected:

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

But when I wrap the whole thing in a function, it's returning the wrong rows. Can you help me see why?

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;

When using the function:

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

it always returns the same result below of multiple rows but obviously wrong rows, why ? can you help here ?

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

Solution

Thanks to @a_horse_with_no_name, now I have two working solutions, one with sql, one with pl/pgsql:

Solution 1 (pl/pgsql)

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;

Solution 2 (sql)

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;

Testing of the code

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');

Both PG stored procedure are working as expected!

Solution 3: A better simplified solution see from Erwin's answer below.

Now case closed !

  • That's a function not an SP – James Anderson Oct 30 '14 at 10:06
  • 3
    @JamesAnderson: Postgres doesn't have real stored procedures. So in the Postgres world the term "procedure" is often used as a synonym for "function" – a_horse_with_no_name Oct 30 '14 at 10:13
  • Ah didn't see the Postgres tag. My mistake – James Anderson Oct 30 '14 at 10:49
8

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;
6

You wrote "case closed", but I'll reopen. There is just too much gone wrong ...

Database design and test setting

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');

Major points

  • Simplify names for better readability.

  • Don't use non-descriptive column names like entity_id. Replaced with useful names.

  • It's good practice to use the same name for columns with identical content. Using patient_id for the FK column in messageq.

  • If you indeed have a varchar PK in your message queue, test with actual varchar values.

  • Simplify INSERT statements.

  • Add an index on messageq.patient_id. This is crucial for performance.

Function

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;

Yes, that's all.
Call:

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

SQL Fiddle (on pg9.2, pg9.3 is overloaded)

Major points

  • No need for a CTE (or even two). That would be a waste of code and time here. A simple query with joins does the job.

  • Using a VARIADIC parameter for simpler calls (optional).
    Related:

  • If (patient_id int, site_held_at) is unique in table patient, you do not need DISTINCT in the query. Else add it.

  • Thanks Erwin for all those good points. But I have noticed when using JOIN USING, it shows me the distinct result as expected while when I use JOIN ON ( SELECT messageq.* FROM patient p JOIN messageq on messageq.patient_id = messageq.patient_id ). it show duplicated result over 110 record. Is this expected ? Both case without using DISTINCT with same data. – Gob00st Oct 31 '14 at 11:16
  • @Gob00st: Your JOIN condition is wrong, would have to be SELECT m.* FROM patient p JOIN messageq m ON m.patient_id = p.patient_id .... – Erwin Brandstetter Oct 31 '14 at 11:23
  • Sorry, my mistake ! – Gob00st Oct 31 '14 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.