sql - 如果找不到值，如何从函数返回值

Question

如果以下函数未返回任何内容，我将尝试返回 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);

Answer 1

说明

问题的根源在于“什么都不是”的模糊定义。

if the following function does not return anything

NULL 不是无，只是不知道它到底是什么。 SQL 中的“无”更像是无行 - 根本没有返回任何内容。当找不到符合条件的行时，通常会发生这种情况。但是当使用聚合函数时，这不会发生，因为，per documentation:

... these functions return a null value when no rows are selected.

avg() 在没有找到任何行时返回 NULL(因此不是“无”)。结果，您得到一行包含 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 here
<子>旧sqlfiddle

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

其他情况

如果您实际上可以从查询中得到没有行，一个简单的COALESCE会失败，因为它是从未执行过。

对于单值结果，您可以像这样包装整个查询:

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

• How to display a default value when no match found in a query?

PL/pgSQL 有能力在实际从函数返回之前进行检查。这也适用于具有一列或多列的多行。有一个 example in the manual演示 FOUND 的使用:

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

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

相关:

• Return setof record (virtual table) from function
• PostgreSQL - Check foreign key exists when doing a SELECT

要始终返回恰好一行，您还可以使用纯SQL:

SELECT foo, bar FROM tbl
UNION ALL
SELECT 'foo_default', 'bar_default'
LIMIT  1;

如果第一个 SELECT 没有返回任何行，则第二个 SELECT 返回带有默认值的行。