Return table type from a function in PostgreSQL

Question

I have a function with RETURNS TABLE, and I want to return certain columns from my source table. When I execute the function, it gives no error but also returns no rows although it should.

What's wrong with my 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
$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

Your function is doing a lot of empty work.

You could replace the tedious and expensive explicit cursor with a FOR loop using a cursor implicitly. But don't bother, and radically simplify with a single query instead. Optionally wrapped 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)
  LANGUAGE sql 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$;

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 is crucial for performance.