sql - PostgreSQL 存储过程不允许使用 SELECT INTO 并抛出错误“INTO used with a command that cannot return data”
问题描述
这个存储过程只是在表中插入一些值，但是当我尝试返回一个类型只是为了显示插入的值时，这让我很痛苦。我已经声明了一个变量（report_tab）和我想要返回的类型（report_building_style_type），但是如果我尝试通过 SELECT INTO 填充它（无论我在哪个选择中尝试）它都会抛出“错误：INTO 与无法返回数据的命令”。
我试图弄清楚这意味着什么以及如何解决它，甚至检查了一些以前的 stackoverflow 相关问题，但实际上找不到答案。
到目前为止，这是我的代码：
CREATE OR REPLACE FUNCTION update_report_building_style(p_year integer)
RETURNS report_building_style_type
AS $$
DECLARE
found_bld building.id_building%TYPE;
report_tab report_building_style_type;
BEGIN
FOR found_bld IN
SELECT m.id_building into report_tab.t_id_building
FROM building m
WHERE EXTRACT(YEAR FROM m.birth) = p_year
LOOP
INSERT INTO report_building_style
SELECT INTO
report_tab.t_id_building,
report_tab.t_ style ,
report_tab.t_ num_firms,
report_tab.t_ stages,
report_tab.t_average_building_height
DISTINCT
T1.id_building,
T1.style,
T2.num_firms,
T3.num_stages,
T4.num_built_buildings,
T5.average_building_height
FROM
(SELECT
x0.id_building,
firm.style
FROM firm,
(SELECT building.name, building.id_building, id_firm
FROM building
INNER JOIN member
ON building.id_building = member.id_building
WHERE building.id_building = found_bld
GROUP BY building.id_building, building.name, id_firm) as x0
WHERE firm.id_firm = x0.id_firm
GROUP BY x0.id_building, x0.name, x0.id_firm, firm.style) AS T1
INNER JOIN
(SELECT x1.id_building, x1.name, COUNT(*) AS num_firms
FROM (SELECT building.name, building.id_building, id_firm
FROM building
INNER JOIN member
ON building.id_building = member.id_building
WHERE building.id_building = found_bld
GROUP BY member.id_firm, building.id_building, building.name) as x1
GROUP BY x1.id_building, x1.name) AS T2
ON T1.id_building = T2.id_building
INNER JOIN
(SELECT x2.id_building, x2.name, COUNT(*) AS num_stages
FROM (SELECT building.name, building.id_building, member.id_firm
FROM building
INNER JOIN member
ON building.id_building = member.id_building
INNER JOIN stage
ON stage.id_firm = member.id_firm
WHERE building.id_building = found_bld
GROUP BY stage.id_stage, member.id_firm, building.id_building, building.name) as x2
GROUP BY x2.id_building, x2.name
ORDER BY x2.id_building) AS T3
ON T3.id_building = T2.id_building
INNER JOIN
(SELECT x3.id_building, x3.name, COUNT(*) AS num_built_buildings
FROM (SELECT building.name, building.id_building
FROM building
INNER JOIN architect
ON architect.id_building = building.id_building
WHERE building.id_building = found_bld
GROUP BY architect.id_building, building.id_building, building.name) as x3
GROUP BY x3.id_building, x3.name
ORDER BY x3.id_building) AS T4
ON T4.id_building = T3.id_building
INNER JOIN
(select b.id_building, SUM (b.height)/COUNT (*) AS average_building_height
from (SELECT building.name, building.id_building, building.height, SUM(building.height) AS building_height
FROM building
INNER JOIN architect
ON architect.id_building = building.id_building
INNER JOIN building
ON building.id_building = architect.id_building
WHERE building.id_building = found_bld
GROUP BY building.id_building, building.name, building.duration, architect.id_building
ORDER BY building.id_building) as b
group by id_building) AS T5
ON T5.id_building = T3.id_building
ORDER BY T1.id_building
ON CONFLICT (id_building, style)
DO UPDATE SET
style = excluded.style,
num_firms = excluded.num_firms,
num_stages = excluded.num_stages,
num_built_buildings = excluded.num_built_buildings,
average_building_height = excluded.average_building_height;
return report_tab;
END LOOP;
END;
$$LANGUAGE plpgsql;
提前谢谢了。