存储过程或函数中的结果集类型变量如何使用。

Question

sudo -u postgres psql -c "CREATE DATABASE sanguo;"
sudo -u postgres psql sanguo -c "CREATE TABLE heroes(id serial, name VARCHAR(20),fight int,intelligence int,city VARCHAR(20),nation VARCHAR(20),items VARCHAR(20));"
sudo -u postgres psql sanguo -c  "INSERT INTO heroes(name, fight, intelligence,nation,city,items) VALUES('刘备',60,65,'蜀','涿','双股剑'),('关羽',96,96,'蜀','解良','青龙偃月刀 赤兔马'),('张飞',98,60,'蜀','涿','丈八蛇矛'),('吕布','100','86','董卓','长安','方天画戟'),('马超','97','86','蜀','天水','铁骑尖'),('赵云','97','90','蜀','蓟','龙胆'),('典韦','96','57','魏','陈留','铁戟'),('许褚','96','65','魏','陈留',''),('甘宁','95','84','吴','建邺','双匕首'),('庞德','95','80','魏','天水',''),('黄忠','94','85','蜀','长沙','猛将弓'),('文丑','94','79','袁绍','蓟',''),('太史慈','93','82','吴','建邺',''),('华雄','92','82','董卓','长安',''),('孙策','92','92','吴','建邺','霸王枪'),('颜良','92','80','袁绍','蓟',''),('张辽','92','93','魏','蓟',''),('魏延','91','81','蜀','信阳',''),('夏侯渊','91','85','魏','陈留',''),('孙坚','90','93','吴','建邺',''),('文鸯','90','77','魏','蓟',''),('夏侯惇','90','89','魏','陈留','碎岩'),('周泰','90','77','吴','建邺',''),('曹彰','88','82','魏','许昌',''),('姜维','88','89','蜀','天水',''),('曹操','85','96','魏','陈留','孟德新书 玉玺 铜雀台赋'),('孙权','83','91','吴','建邺',''),('诸葛亮','60','98','蜀','南阳',''),('董卓','60','50','董卓','长安','魔王扑'),('袁绍','60','60','袁绍','长安','王道剑');"

• 以上为heroes表的建立
• 想要在存储过程中通过变量赋值实现如下的嵌套查询效果

\c sanguo

SELECT 
    name
FROM
    heroes
WHERE
    nation =(
        SELECT
            nation
        FROM
            heroes
        WHERE
            name = '曹操'
    )
;

• 目前建立 存储过程如下所示

\c sanguo
CREATE OR REPLACE PROCEDURE test_proc()
LANGUAGE plpgsql
AS $$
DECLARE
    var_nation TEXT;
BEGIN
    SELECT nation
    INTO var_nation 
    FROM heroes
    WHERE name='刘备'
    LIMIT 1;
    RAISE NOTICE 'nation: %',var_nation ;
END; $$
;
CALL test_proc();

• 目前可以输出蜀
• 但无法输出蜀国中所有武将

---

• 如果不用存储过程完成当前任务，改用函数，返回结果集，有应该如何书写呢？

Answer 1

在PostgreSQL中，如果你想在存储过程或者函数中返回一个结果集，你可以使用TABLE类型或者SETOF修饰符。

下面是一个存储过程的例子，它返回一个TABLE类型的结果集：

CREATE OR REPLACE PROCEDURE test_proc()
LANGUAGE plpgsql
AS $$
DECLARE
    var_nation TEXT;
BEGIN
    var_nation := (SELECT nation FROM heroes WHERE name='刘备' LIMIT 1);
    RETURN QUERY SELECT name FROM heroes WHERE nation = var_nation;
END; $$
;

调用这个存储过程的方法如下：

CALL test_proc();

然而，需要注意的是，在PostgreSQL中，存储过程不直接返回结果集，它只能通过OUT参数或者通过RAISE NOTICE来返回信息。所以，如果你想在存储过程中返回一个结果集，你可能需要把它放入一个临时表中，然后在存储过程之外查询这个临时表。

相比之下，函数可以直接返回一个结果集。下面是一个函数的例子，它返回一个SETOF TEXT类型的结果集：

CREATE OR REPLACE FUNCTION test_func()
RETURNS SETOF TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    var_nation TEXT;
BEGIN
    var_nation := (SELECT nation FROM heroes WHERE name='刘备' LIMIT 1);
    RETURN QUERY SELECT name FROM heroes WHERE nation = var_nation;
END; $$
;

调用这个函数的方法如下：

SELECT * FROM test_func();

这个函数将返回一个包含所有满足条件的武将名字的结果集。