从PostgreSQL中的函数返回表类型

Question

我有一个带有RETURNS TABLE的函数，我想从我的源表中返回某些列。当我执行这个函数时，它不会出现错误，但也不会返回任何行，尽管它应该返回。

我的功能怎么了？

CREATE OR REPLACE FUNCTION ccdb.fn_email_details_auto()
  RETURNS TABLE (code integer, area smallint, action smallint
               , flag smallint, ucount  integer, view_cnt integer) AS
$BODY$
DECLARE 
   sec_col refcursor;
   cnt integer;
   sec_code ccdb.update_qtable%ROWTYPE;
BEGIN
   SELECT COUNT(DISTINCT section_code)  INTO cnt
   FROM ccdb.update_qtable
   WHERE entry_time::date = now()::date - interval '1 day';

   OPEN sec_col FOR
   SELECT * FROM ccdb.update_qtable
   WHERE entry_time::date = now()::date - interval '1 day';

   FOR i IN 1..cnt
   LOOP
      FETCH sec_col INTO sec_code;

      PERFORM section_code, ddu_area, ddu_action, status_flag
            , ccdb_ucount, ccdb_view_cnt
      FROM ccdb.update_qtable
      WHERE entry_time::date = now()::date - interval '1 day'
      AND section_code =  sec_code.section_code
      ORDER BY ddu_area, ddu_action;
   END LOOP;

   CLOSE sec_col;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;

Answer 1

通过对我使用的SELECT语句的返回查询，我能够解决这个问题。下面提到的查询帮助我实现了我的需求。

CREATE OR REPLACE FUNCTION ccdb.fn_email_details_auto()
  RETURNS TABLE (code integer, area smallint, action smallint, flag smallint, ucount integer, view_cnt integer) AS
$BODY$

DECLARE 
sec_col refcursor;
cnt integer;
sec_code ccdb.update_qtable%ROWTYPE;

BEGIN

SELECT COUNT(DISTINCT section_code)
INTO cnt
FROM ccdb.update_qtable
WHERE entry_time::date = now()::date - interval '1 day';

OPEN sec_col FOR
SELECT DISTINCT ON (section_code)* FROM ccdb.update_qtable WHERE entry_time::date = now()::date - interval '1 day';

FOR i IN 1..cnt
LOOP

FETCH sec_col INTO sec_code;

RETURN QUERY 
SELECT section_code, ddu_area, ddu_action, status_flag, ccdb_ucount, ccdb_view_cnt
FROM ccdb.update_qtable
WHERE entry_time::date = now()::date - interval '1 day' AND section_code = sec_code.section_code
ORDER BY ddu_area, ddu_action;

END LOOP;

CLOSE sec_col;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;