How to return a value from a function if no value is found

Question

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;

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);

Answer 1

Explanation

The root of the problem is the fuzzy definition of "not anything".

if the following function does not return anything

NULL is not nothing, it's just unknown what it is exactly. "Nothing" in terms of SQL would rather be no row - nothing returned at all. That typically happens when no qualifying row is found. But when using aggregate functions, that cannot happen because, per documentation:

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

avg() returns NULL when no rows are found (so not "nothing"). You get a row with a NULL value as result - which overwrites your init value in the code you demonstrate.

Solution

Wrap the result in COALESCE. Demonstrating a much simpler SQL function:

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
_{Old sqlfiddle}

The same can be used in a PL/pgSQL function. This function can be STABLE, might help with performance in the context of bigger queries.

Other cases

If you actually can get no row from a query, a simple COALESCE would fail, because it's never executed.

For a single value result you can just wrap the whole query like:

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 has the ability to check before actually returning from the function. This works for multiple rows with one or more columns, too. There is an example in the manual demonstrating the use of FOUND:

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

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

Related:

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

To always return exactly one row, you can also use pure SQL:

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

If the first SELECT returns no row, the second SELECT returns a row with defaults.