这两个PostgreSQL函数等效吗？(RETURNS TABLE和RETURNS SETOF)

Question

我正在同时处理两个PostgreSQL安装：我的本地环境和真实的远程服务器。不幸的是，服务器有一个旧版本（8.3.11），而我的本地环境是新版本（9.4）。

目前我没有办法更新远程服务器，所以我正在将一个在9.4中运行良好的函数（它使用RETURNS TABLE）转换为一个应该可以在8.3.11中正常运行的函数（它应该使用RETURNS SETOF）。

但是，虽然本地环境函数能够良好地工作并且给出好的结果，但远程函数总是没有结果（使用完全相同的表！）

那么，这两个函数完全等效吗？

本地环境的新函数如下：

CREATE OR REPLACE FUNCTION pra2.GetGamesOnDate(date) 
  RETURNS TABLE (game_date date, is_home varchar, is_away varchar) AS $$
BEGIN
   RETURN QUERY  
   SELECT g.game_date, p1.team_name AS plays_at_home, p2.team_name AS plays_away
   FROM pra2.game g
   JOIN pra2.team p1 ON g.is_home = p1.team_id
   JOIN pra2.team p2 ON g.is_away = p2.team_id
   WHERE g.game_date = $1;
   IF NOT FOUND THEN
      RAISE EXCEPTION 'No hay partidos para la fecha %.', $1;
   END IF;
   RETURN;
END
$$
   LANGUAGE plpgsql;

下面是我修改过的使用 SETOF 的函数

CREATE TYPE return_type AS 
(game_date date,
is_home varchar,
is_away varchar);

CREATE OR REPLACE FUNCTION pra2.GetGamesOnDate(date) 
  RETURNS SETOF return_type AS $$ 
DECLARE
   _rec return_type;
BEGIN
   RETURN QUERY  
   SELECT g.game_date, p1.team_name AS plays_at_home, p2.team_name AS plays_away
   FROM pra2.game g
   JOIN pra2.team p1 ON g.is_home = p1.team_id
   JOIN pra2.team p2 ON g.is_away = p2.team_id
   WHERE g.game_date = $1;
   IF NOT FOUND THEN
   RAISE EXCEPTION 'No hay partidos para la fecha %.', $1;
   END IF;
      RETURN next _rec;
END
$$
   LANGUAGE plpgsql;

这段代码没有任何错误提示，程序可以正常运行，但是没有返回任何结果（总是引发异常消息），因此我想知道在 SETOF 查询中是否设置有误...

- telex-wap

---

你忘记添加你提到的（逐字）错误信息了。它应该总是在那里。 - Erwin Brandstetter

Answer 1

根据文档，PostgreSQL 8.3中的 RETURN QUERY 不会设置 FOUND 。(PostgreSQL 9.1的相关文档位于http://www.postgresql.org/docs/9.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS; 相应的语句在 PostgreSQL 8.3 的文档中没有显示在http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS。因此，您的 IF NOT FOUND 检查未达到预期效果。

老实说，在 PostgreSQL 8.3 中实现这个功能最好的方法我也不确定。其中一个选择是编写以下代码：

CREATE OR REPLACE FUNCTION pra2.GetGamesOnDate(date) 
RETURNS SETOF return_type AS $$ 
DECLARE _rec return_type;
        has_rec boolean;
BEGIN
    has_rec := false;
    FOR _rec IN
        SELECT g.game_date, p1.team_name AS plays_at_home, p2.team_name AS plays_away
        FROM pra2.game g
        JOIN pra2.team p1 ON g.is_home = p1.team_id
        JOIN pra2.team p2 ON g.is_away = p2.team_id
        WHERE g.game_date = $1
    LOOP
        has_rec := true;
        RETURN NEXT _rec;
    END LOOP;
    IF NOT has_rec THEN
        RAISE EXCEPTION 'No hay partidos para la fecha %.', $1;
    END IF;
END
$$ LANGUAGE plpgsql;

（免责声明：未经测试。）

- ruakh

---

大师！就像您说的那样，它完全奏效了！我仍然会抱怨他们使用8.3的事实...但至少您给了我很大的推动。我最近才开始学习postgreSQL，并通过阅读较新版本的文档来进行学习，所以这有点令人困惑。 - telex-wap

Answer 2

PL/pgSQL在PostgreSQL 8.3中对于RETURN QUERY不会设置特殊变量FOUND。这个功能是在Postgres 8.4中添加的。

但是，您仍然无需求助于更复杂和昂贵的循环。您可以使用另一种方法GET DIAGNOSTICS _ct = ROW_COUNT;，该方法在手册中紧接着FOUND进行了解释:

CREATE TYPE return_type AS ( ...);

CREATE OR REPLACE FUNCTION pra2.GetGamesOnDate(date) 
  RETURNS SETOF return_type AS
$func$ 
DECLARE
   _ct int;
BEGIN
   RETURN QUERY  
   SELECT g.game_date, p1.team_name, p2.team_name
   FROM   pra2.game g
   JOIN   pra2.team p1 ON g.is_home = p1.team_id
   JOIN   pra2.team p2 ON g.is_away = p2.team_id
   WHERE  g.game_date = $1;

   GET DIAGNOSTICS _ct = ROW_COUNT;  -- number of returned rows.

   IF _ct = 0 THEN
      RAISE EXCEPTION 'No hay partidos para la fecha %.', $1;
   END IF;
END
$func$  LANGUAGE plpgsql;

此外，您原始版本中的变量_rec return_type和RETURN next _rec;没有任何作用。现在该函数是等效的。您甚至可以在Postgres 9.4中使用相同的函数。相关答案如下：

• 返回确切行数的查询

另外：在Postgres中，像GetGamesOnDate这样的驼峰命名标识符是一个不好的主意。坚持使用合法的小写名称。

- Erwin Brandstetter

---

谢谢提供另一种版本，我会记住你的建议！ - telex-wap