从PostgreSQL中的函数返回表类型 [英] Return Table Type from A function in PostgreSQL
问题描述
我有一个函数,该函数的返回类型为TABLE,我想将表中的某些列转换为该RETURN TABLE类型的功能.当我执行该函数时,尽管它应该根据我的条件返回一些记录,但它没有给出错误但没有返回记录. 以下是我编写的代码,有人可以让我知道我哪里出错了吗?
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;
推荐答案
您的函数正在做很多的空工作.
Your function is doing a lot of empty work.
您可以使用 FOR
循环进行隐式简化游标,而不是更繁琐和昂贵的显式游标.
仔细观察发现,您根本不需要任何这些.通过一个简单的查询彻底简化.我将其包装到一个SQL函数中:
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
代替:
WHERE entry_time::date = now()::date - interval '1 day'
替代方法是可精,并且可以在entry_time
上使用普通索引,该索引应对性能至关重要.
The alternative is sargable and can use a plain index on entry_time
, which should be crucial for performance.
这篇关于从PostgreSQL中的函数返回表类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!