I'm trying to create a function that will cast boolean values to 't' or 'f', or '' if NULL.
CREATE OR REPLACE FUNCTION bool(b BOOLEAN) RETURNS VARCHAR
AS $$
BEGIN
IF b IS NULL THEN
RETURN '';
END IF;
IF b THEN
RETURN 't';
ELSE
RETURN 'f';
END IF;
END;
$$ LANGUAGE PLPGSQL CALLED ON NULL INPUT;
However, the following always returns NULL. What gives?
SELECT bool(NULL)
bool is also a built-in function and type cast (because there is a data type with that name).
You need to either explicitly reference your function by prefixing it with the schema:
select public.bool(null);
or give your function a different name.
Unrelated, but: your function can be simplified to:
CREATE OR REPLACE FUNCTION bool(b BOOLEAN)
RETURNS VARCHAR
AS $$
select coalesce(case when b then 't' else 'f' end, '');
$$
language sql
called on null input;
b::text yields 'true' which is why I have the function in the first place, but this was definitely it. Thanks!