使用postgres函数从多个表返回插入计数

Question

我有一个函数，它将值1个月的数据从主表插入到历史表中。由于postgres没有存储过程，我已经将插入和删除逻辑声明为一个函数。我想知道是否有一种方法可以将插入和删除的计数作为函数的输出返回。以下是代码。

CREATE FUNCTION public.insertDelete(integer)
RETURNS void
LANGUAGE plpgsql
AS $BODY$
DECLARE
saveTime timestamp;
BEGIN
saveTime := now();

INSERT INTO public.hist_One
(select * from public.main_One
WHERE udate < (saveTime - ($1::text || ' months')::interval));
INSERT INTO public.hist_Two
(select * from public.main_Two
WHERE udate < (saveTime - ($1::text || ' months')::interval));
INSERT INTO public.hist_Three
(select * from public.main_Three
WHERE udate < (saveTime - ($1::text || ' months')::interval));

delete from public.main_One
where udate < (saveTime - ($1::text || ' months')::interval);
delete from public.main_Two
where udate < (saveTime - ($1::text || ' months')::interval);
delete from public.main_Three
where udate < (saveTime - ($1::text || ' months')::interval);

END;
$BODY$;

如果我试图返回Query，那么代码看起来像这个

CREATE FUNCTION public.insertDelete(integer)
RETURNS Table(Hist_One int, Main_one int)
LANGUAGE plpgsql
AS $BODY$
DECLARE
saveTime timestamp;
BEGIN
saveTime := now();

INSERT INTO public.hist_One
(select * from public.main_One
WHERE udate < (saveTime - ($1::text || ' months')::interval));
RETURN QUERY select count(*) from public.main_One
WHERE udate < (saveTime - ($1::text || ' months')::interval)

但问题是，我想为所有表返回，而使用RETURN QUERY，我只能为一个表返回。那么，我如何返回一个表，该表将列出对Hist表的插入和对Main表的删除？

Answer 1

您可以使用诊断ROW_COUNT来获取每次插入影响的行数，然后只需将函数的返回类型更改为整数并"返回"总行数：

CREATE FUNCTION public.insertDelete(integer)
RETURNS integer
LANGUAGE plpgsql
AS $BODY$
DECLARE
saveTime timestamp;
rowcount integer;
totalrow integer;
BEGIN
saveTime := now();
total_rows := 0;
INSERT INTO public.hist_One
(select * from public.main_One
WHERE udate < (saveTime - ($1::text || ' months')::interval));
GET DIAGNOSTICS rowcount = ROW_COUNT;
totalrow = totalrow + rowcount;
INSERT INTO public.hist_Two
(select * from public.main_Two
WHERE udate < (saveTime - ($1::text || ' months')::interval));
GET DIAGNOSTICS rowcount = ROW_COUNT;
totalrow = totalrow + rowcount;
INSERT INTO public.hist_Three
(select * from public.main_Three
WHERE udate < (saveTime - ($1::text || ' months')::interval));
GET DIAGNOSTICS rowcount = ROW_COUNT;
totalrow = totalrow + rowcount;
-- the rest of your function here    
return totalrow;
END;
$BODY$;

完全无关，可能也没关系，但我会以稍微不同的方式计算月数：

INSERT INTO public.hist_One
select * from public.main_One
WHERE udate < saveTime - interval '1 month' * $1;

或者最好声明一次值：

DECLARE
from_date date;
from_date := current_date - interval '1 month' * $1;

然后将其用于每个查询：

INSERT INTO public.hist_One
select * from public.main_One
WHERE udate < from_date;

这样做的好处是，我可以看到动态计算会抑制索引的使用，而这将作为静态值发送，并且肯定会利用"udate"列上的任何索引。

此外，我无法键入其名称的注释是正确的——PostgreSQL确实支持版本11的存储过程……尽管在这种情况下，由于您希望返回行数，因此函数似乎更好。

Answer 2

在思考了返回插入计数和删除计数的最佳方法后，我决定创建一个名为loadinfo的单独表，并作为函数的一部分，在加载到历史记录表完成后将计数插入到loadinfo表中。通过这种方式，我可以在历史表中插入和删除以供参考

CREATE FUNCTION public.insertfour_stats(integer)
RETURNS void
LANGUAGE plpgsql
AS $BODY$
DECLARE
saveTime timestamp;
from_date date;
FuncInvoke varchar;
BEGIN
saveTime := now();
from_date := current_date - interval '1 month'*$1;
FuncInvoke := 'Insert to Hist';
INSERT INTO public.Hist_One
(select * from public.Main_One
WHERE udate < from_date);
INSERT INTO public.Hist_Two
(select * from public.Main_Two
WHERE udate < from_date);
INSERT INTO public.loadinfo(bdate, FuncInvoke, summary_hist_insert, account_hist_insert)
(SELECT saveTime, FuncInvoke
, (select count(*) from public.Main_One WHERE udate < from_date)
, (select count(*) from public.Main_Two WHERE udate < from_date));
delete from public.Main_One
where udate < from_date;
delete from public.Main_Two
where udate < from_date;
END;
$BODY$;