Syntax error creating function in Snowflake

I am unable to fix a syntax error that I keep getting when trying to create a function in Snowflake. This works fine when I create a procedure, but whenever I try to create a function with a variable declaration, I keep getting the error below. I want this to be a function since it only returns a single, scalar value.

Syntax error: unexpected 'l_emp_excess_balance'.t

Here is the code:

CREATE OR REPLACE FUNCTION MDT_PRESDEV_DB_ADMIN.PERS.begin_bal_emp_excess_comp(psid VARCHAR)
RETURNS NUMBER
AS
$$
DECLARE
  l_emp_excess_balance NUMBER;
BEGIN
  SELECT COALESCE(v.peec_excess_leave_hrs, 0)
  INTO l_emp_excess_balance
  FROM MDT_PRESDEV_DB_ADMIN.pers_excess_xmpt_comp_leave v
  WHERE v.peec_ps_id = psid
    AND v.peec_system = 'PERS'
    AND v.peec_year = (
      SELECT MAX(m.peec_year)
      FROM MDT_PRESDEV_DB_ADMIN.pers_excess_xmpt_comp_leave m
      WHERE m.peec_system = 'PERS'
    );
  RETURN COALESCE(l_emp_excess_balance, 0);
END;
$$;

I tried creating a procedure instead of a function. That worked, but I would much rather this just be a function.

Scalar SQL UDFs expects an expression and not Scripting block DECLARE BEGIN END:

CREATE OR REPLACE FUNCTION MDT_PRESDEV_DB_ADMIN.PERS.begin_bal_emp_excess_comp(psid VARCHAR)
RETURNS NUMBER
AS
$$
  SELECT COALESCE(v.peec_excess_leave_hrs, 0)
  FROM MDT_PRESDEV_DB_ADMIN.pers_excess_xmpt_comp_leave v
  WHERE v.peec_ps_id = psid
    AND v.peec_system = 'PERS'
    AND v.peec_year = (
      SELECT MAX(m.peec_year)
      FROM MDT_PRESDEV_DB_ADMIN.pers_excess_xmpt_comp_leave m
      WHERE m.peec_system = 'PERS'
    )
$$;

Side note: if no row is found due to filtering then the query will return NULL even though COALESCE is used.

CREATE OR REPLACE FUNCTION test()
RETURNS NUMBER
AS
$$
  SELECT COALESCE(col, 0)
  FROM (VALUES (1)) AS sub(col)
  WHERE 1=2
$$;

SELECT test();
-- null

To ensure that there is always at least one row returned an aggregate function before COALESCE can be used. Aggregate function should be chosen carefully especially if it is possible to get more than a single row after filtering:

CREATE OR REPLACE FUNCTION test()
RETURNS NUMBER
AS
$$
  SELECT COALESCE(MIN(col), 0)
  FROM (VALUES (1)) AS sub(col)
  WHERE 1=2
$$;

SELECT test();
-- 0

Snowflake Scripting user-defined functions (UDFs) are going to enable Scripting block for UDFs(9.23 Release Notes: Preview)