Return Table Type from A function in PostgreSQL

问题:

I have a function from which has a return type as TABLE, and I want to get certain columns from my table into that RETURN TABLE type for my functionality. When I execute the function, it gives no error but returns no records although it should return some records based on the condition that I have. Below is the code that I have written, can someone let me know where have I gone wrong?

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;

回答1:

Your function is doing a lot of empty work.

You could simplify with a FOR loop with implicit cursor instead of the more tedious and expensive explicit cursor.
On a closer look it turns out you don't need any of this at all. Radically simplify with a simple query. I wrapped it into an SQL function:

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
$func$

SELECT u.section_code, u.ddu_area, u.ddu_action, u.status_flag
     , u.ccdb_ucount, u.ccdb_view_cnt
FROM   ccdb.update_qtable u
WHERE  u.entry_time >= now()::date - 1
AND    u.entry_time <  now()::date        -- sargable!
ORDER  BY u.section_code, u.ddu_area, u.ddu_action;

$func$  LANGUAGE sql;

Should be much faster while returning the same.
Also, use this:

WHERE  u.entry_time >= now()::date - 1
AND    u.entry_time <  now()::date

instead of:

WHERE entry_time::date = now()::date - interval '1 day'

The alternative is sargable and can use a plain index on entry_time, which should be crucial for performance.

回答2:

I was able to solve this issue by using a RETURN QUERY for the SELECT statement where I was using PERFORM. The below mentioned query helped me achieve my requirement.

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;