COALESCE doesn't seem to fire in Postgres 12 SQL stored function

Question

Using Postgres 12.x, I've just run into a weird problem. For some reason, I'm not getting COALESCE to work in a stored SQL function. Meaning, I get NULL instead of my first non-null value.

I suspect that I've missed something obvioius, but can't see it. Can anyone spot what the problem is? I've built a stand-alone example that reproduces the issue.

Thanks

Test Setup

BEGIN;

------------------------------------
-- Define table
------------------------------------

DROP TABLE IF EXISTS lookup_table CASCADE;

CREATE TABLE IF NOT EXISTS lookup_table (
    id    uuid    NOT NULL DEFAULT NULL, -- Values are geneated by an outside system.
    name_ citext  NOT NULL DEFAULT NULL
);

------------------------------------
-- Seed
------------------------------------
INSERT INTO lookup_table (id, name_) 
     VALUES  ('6ea225f3-9819-49eb-bdb8-f3ae23af6337', 'John'),
             ('ae2282c6-ca9b-413f-b182-a1ca69fc3c78', 'Alice'),
             ('7ee1ce1b-4efc-426c-a6f8-5b2375cb357e', 'Magrite'),
             ('71c3d96a-3ac7-454e-bfff-e5551428c017', 'Arjun');

------------------------------------
-- Define the lookup fnction
------------------------------------
CREATE OR REPLACE FUNCTION lookup_function (id_in uuid)
     RETURNS citext AS

$BODY$

-- For some reason, COALESCE doesn't work here, but does outside of a function. Weird.
-- Tried with CTE, same same.
SELECT COALESCE (name_ , '') FROM lookup_table WHERE id = id_in;

$BODY$
  LANGUAGE SQL;

------------------------------------
-- Test it out
------------------------------------
SELECT 'Alice' AS description,
       lookup_function ('ae2282c6-ca9b-413f-b182-a1ca69fc3c78') AS result
       UNION ALL
       
SELECT 'Should return an empty string, returns NULL instead'           AS description,
       lookup_function ('00000000-0000-0000-0000-000000000000') AS result

Standard Call

For comparison, here's a standard call right in a SELECT that works fine:

select coalesce (name_, '') 
  from lookup_table
 where id = '00000000-0000-0000-0000-000000000000';
 -- Returns an empty string, not NULL.

But, in a SQL function, this expression returns NULL instead.

CTE Attempt

Here's my stab at using a CTE to help out in the function, but I still get back NULL.

------------------------------------
-- Define the lookup fnction
------------------------------------
CREATE OR REPLACE FUNCTION lookup_function (id_in uuid)
     RETURNS citext AS

$BODY$

WITH lookup_result AS (
   select name_ from lookup_table where id = id_in 
 )
 
SELECT CASE WHEN name_ IS NULL THEN ''
            ELSE name_ END
       FROM lookup_result

$BODY$
  LANGUAGE SQL;

Answer 1

The lookup function doesn't return anything from the select statement inside it as the where clause fails. The coalesce is never executed therefore. A null is returned by default.

You can rewrite the select as:
select coalesce((SELECT name_ FROM lookup_table WHERE id = id_in),'');