Postgresql

CTE 按預期工作，但在包裝到函式中時不能

  • October 31, 2014
--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');

當我試圖從我感興趣的站點中的消息表中查找所有消息時，我寫了一個 CTE，它工作正常，假設我對站點 11111 和 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

結果如預期：

"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"

解決方案

感謝@a_horse_with_no_name，現在我有兩種可行的解決方案，一種使用 sql，一種使用 pl/pgsql：

解決方案 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;

解決方案 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;

程式碼測試

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

兩個 PG 儲存過程都按預期工作！

解決方案 3：更好的簡化解決方案，請參見下面的 Erwin 回答。

現已結案！

我認為這是因為您只返回查詢結果的第一行。

將select ... into ...僅檢索一行，並且query select * from result僅返回該單條記錄：

你也不需要 PL/pgSQL 函式，一個普通的 SQL 函式就可以了：

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;

請注意，CTE 中的 order by 並不是真正有用。您必須對最終選擇進行排序，而不是中間步驟。

如果你確實需要 PL/pgSQL，因為你在函式中做了更多的事情，你應該簡單地將其更改為：

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;

引用自：https://dba.stackexchange.com/questions/81460

相關問答

Postgresql

PostgresSQL 使用數組中的參數值動態執行

  • January 29, 2022
檢視問答
Postgresql

如何在 plpgsql 過程中參數化 DDL？

  • November 17, 2021
檢視問答
Postgresql

觸發功能不存在，但我很確定它存在

  • October 23, 2021
檢視問答
Postgresql

SELECT … 使用 LIMIIT，但向前迭代獲取其他記錄？

  • July 30, 2021
檢視問答
Postgresql

如何在 postgresql 中創建一個帶有兩個參數的函式以從列表中返回計數？

  • April 29, 2021
檢視問答
Postgresql

如何更改 PostgreSQL PL/pgSQL 儲存過程中事務的預設隔離級別？

  • April 6, 2021
檢視問答