如果没有找到任何值，如何从函数返回一个值 [英] How to return a value from a function if no value is found

问题描述

如果以下函数没有返回任何内容，我试图返回 0.0 ：

I'm attempting to return 0.0 if the following function does not return anything:

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 不起作用。有没有人有任何想法如何解决这个问题？

I've tried searching for it and found that COALESCE does not work. Does anyone have any ideas how to solve this?

表结构：

Table structure:

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

示例数据：

Example data:

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

推荐答案

这是我使用的脚本。正如你所看到的，我运行PostgreSQL 9.4.1。
我使用HeidiSQL来启动查询。
您确定您正确更新了您的功能吗？
我刚才注意到你在原始文章中使用了一个不同的函数（'player_height'）而不是'get_height'。

Here is the script I used. As you can see I run PostgreSQL 9.4.1. I used HeidiSQL to launch the queries. Are you sure that you correctly updated your function? I just noted that you used a different function ('player_height') in a comment instead of 'get_height' in you original post.

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

这篇关于如果没有找到任何值，如何从函数返回一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！