如何在函数中未找到值时返回一个值

Question

如何在函数中未找到值时返回一个值

4

我想要在以下函数不返回任何内容时返回0.0：

CREATE OR REPLACE FUNCTION get_height(firstn VARCHAR, lastn VARCHAR)
  RETURNS FLOAT AS
$$
DECLARE
    height FLOAT = 0.0;
BEGIN
    SELECT into height AVG(((p.h_feet * 12) + p.h_inches) * 2.54)
    FROM player p
    WHERE p.firstname = firstn AND p.lastname = lastn; 

    RETURN height;
END;
$$ LANGUAGE plpgsql;

我尝试搜索过，发现 COALESCE 不起作用。有人有什么解决办法吗？

表结构：

 create table player(
                     firstname text
                    ,lastname text
                    ,h_feet INT
                    ,h_inches INT
                    );

示例数据：

  insert into player values ('Jimmy','Howard',6,2);

- user3376703

是的，我找到了。我发现在将COALESCE添加到函数后重命名了我的函数，并且我无意中使用了旧函数。我感觉像个白痴。 - user3376703

4个回答

1

这是我使用的脚本。如您所见，我运行的是PostgreSQL 9.4.1。我使用HeidiSQL来启动查询。您确定您正确地更新了函数吗？我注意到您在注释中使用了不同的函数（'player_height'），而不是在原始帖子中使用的'get_height'函数。

select version();
-- PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit

delimiter //

CREATE OR REPLACE FUNCTION get_height(firstn VARCHAR, lastn VARCHAR)         
RETURNS FLOAT AS $$
DECLARE
height FLOAT = 0.0;
BEGIN
    SELECT into height AVG(((p.h_feet * 12) + p.h_inches) * 2.54)
    FROM players p
    WHERE p.firstname = firstn AND p.lastname = lastn; 
        return coalesce(height, 0.0);
END;
$$ LANGUAGE plpgsql;

delimiter;

CREATE TABLE players (
     firstname varchar(40),
     lastname  varchar(40),
     h_feet int,
     h_inches int);


insert into players values ('Jimmy', 'Howard', 6, 2);

select * from get_height('Jimmy', 'Howard');
-- gives 187.96

select * from get_height('Random', 'Guy');
-- gives 0

- PhillipD

我正在使用 postgreSQL 8.4.20，但由于不是我的服务器，因此无法更新它。这可能就是为什么它不能工作的原因吗？ - user3376703

0

return coalesce(height, 0::decimal(10,2));应该可以解决问题。

- plasticide

@user3376703，为什么这个解决方案不起作用？我刚尝试了一下，它运行得很好。 - PhillipD

刚刚尝试了SELECT * FROM player_height('DNE','DNE');，但它只是返回了一个空列。 - user3376703

SELECT player_height('DNE', 'DNE'); 在这里运行正常。 - PhillipD

你在使用Postgres吗？也许有所不同？我真的不知道为什么。 - user3376703

你的选择需要包含函数调用。select player_height('dne', 'dne') as height - plasticide

0

尝试使用未找到条件

SELECT ... into ...
FROM ...
WHERE ...; 

if not found then
  RETURN 0;
end if;

- Yevgeniy Afanasyev

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

解释

问题的根源在于“没有任何东西”这一模糊定义。

如果以下函数没有返回任何值

NULL 不是 nothing，它只是未知确切的内容。在 SQL 中，“nothing”的意思更应该是没有行 - 根本没有返回任何东西。这通常发生在找不到符合条件的行时。但是，在使用聚合函数时，这种情况不可能发生，因为根据文档：

... 当未选择任何行时，这些函数会返回一个 null 值。

avg() 在找不到行时返回 NULL（因此不是“nothing”）。您将获得一个包含 NULL 值的行作为结果 - 它覆盖了您演示代码中的初始化值。

解决方法

将结果用 COALESCE 包装起来。演示一个更简单的 SQL 函数：

CREATE OR REPLACE FUNCTION get_height_sql(firstn varchar, lastn varchar)
  RETURNS float
  LANGUAGE sql STABLE AS
$func$
SELECT COALESCE(avg(((p.h_feet * 12) + p.h_inches) * 2.54)::float, 0)
FROM   player p
WHERE  p.firstname = firstn
AND    p.lastname = lastn
$func$;

db<>fiddle 这里
_{旧版sqlfiddle}

同样可以在PL/pgSQL函数中使用。这个函数可以是STABLE，在更大的查询上下文中能提高性能。

其他情况

如果实际上从查询中确实无法获得任何行，那么简单的COALESCE 会失败，因为它永远不会被执行。

对于一个单值结果，你可以将整个查询包装起来：

SELECT COALESCE((SELECT some_float FROM ... WHERE ... LIMIT 1), 0) AS result

如何在查询中未找到匹配项时显示默认值？

PL/pgSQL有检查并在实际从函数返回之前进行处理的能力。这对于具有一个或多个列的多行也适用。手册中有一个示例演示了FOUND的使用：

...
RETURN QUERY SELECT foo, bar ...;

IF NOT FOUND THEN
    RETURN QUERY VALUES ('foo_default'::text, 'bar_default'::text);
END IF;
...