WITH FUNCTION/PROCEDURE

WITH
  FUNCTION func_name ( [ param_list ] ) RETURN return_type
  { IS | AS }
  [ declare_section ]
  BEGIN
      statements
  END [ func_name ] ;

  PROCEDURE proc_name ( [ param_list ] )
  { IS | AS }
  [ declare_section ]
  BEGIN
      statements
  END [ proc_name ] ;

  cte_name AS ( SELECT ... )
SELECT ...

WITH
  FUNCTION double_it(n NUMBER) RETURN NUMBER AS
  BEGIN RETURN n * 2; END;
SELECT double_it(5) FROM dual;
-- Expected output: 10

WITH
  FUNCTION tax(amt NUMBER) RETURN NUMBER AS
  BEGIN RETURN amt * 0.1; END;
  orders AS (SELECT 100 AS amount)
SELECT amount, tax(amount) FROM orders;
-- Expected output: 100 | 10

WITH
  FUNCTION add1(n NUMBER) RETURN NUMBER AS BEGIN RETURN n+1; END;
  FUNCTION mul2(n NUMBER) RETURN NUMBER AS BEGIN RETURN n*2; END;
SELECT mul2(add1(3)) FROM dual;
-- Expected output: 8

WITH
  FUNCTION factorial(n NUMBER) RETURN NUMBER AS
  BEGIN
    IF n <= 1 THEN RETURN 1; END IF;
    RETURN n * factorial(n-1);
  END;
SELECT factorial(5) FROM dual;
-- Expected output: 120

-- Correct: PROCEDURE may declare OUT parameters
WITH
  PROCEDURE swap(val IN NUMBER, result OUT NUMBER) AS
  BEGIN
    result := val * 10;
  END;
SELECT 1 FROM dual;  -- the procedure is called by another subprogram inside the same
                     -- WITH block, not directly from a SELECT expression

-- Error: FUNCTION does not allow OUT parameters
WITH
  FUNCTION bad_func(val NUMBER, result OUT NUMBER) RETURN NUMBER AS
  BEGIN RETURN val; END;
SELECT bad_func(5) FROM dual;
-- Expected output: ERROR: WITH FUNCTION "bad_func" cannot declare OUT or IN OUT parameters

WITH
  FUNCTION calc(n NUMBER DEFAULT 10) RETURN NUMBER AS
  BEGIN RETURN n * 2; END;
SELECT calc() FROM dual;
-- Expected output: 20

WITH
  FUNCTION safe_div(a NUMBER, b NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN a / b;
  EXCEPTION
    WHEN OTHERS THEN RETURN NULL;
  END;
SELECT safe_div(1, 0) FROM dual;
-- Expected output: NULL

-- Allowed: INSERT INTO ... WITH FUNCTION ... SELECT ... (Oracle-compatible form)
WITH
  FUNCTION get_bonus(sal NUMBER) RETURN NUMBER AS
  BEGIN RETURN sal * 1.2; END;
INSERT INTO emp_bonus (empno, bonus)
SELECT empno, get_bonus(sal) FROM emp WHERE deptno = 10;

WITH
  FUNCTION add_tax(n NUMBER) RETURN NUMBER AS
  BEGIN RETURN n * 1.1; END;
SELECT * FROM (SELECT add_tax(amount) AS total FROM orders);

WITH
  FUNCTION dup(n NUMBER) RETURN NUMBER AS BEGIN RETURN n; END;
  FUNCTION dup(n NUMBER) RETURN NUMBER AS BEGIN RETURN n * 2; END;
SELECT dup(1) FROM dual;
-- Expected output: ERROR: WITH clause function "dup" is defined more than once with the same argument types

-- Attempting to use WITH FUNCTION syntax in PG_PARSER mode
SET compatible_db = PG_PARSER;
WITH FUNCTION foo(n NUMBER) RETURN NUMBER AS BEGIN RETURN n; END;
SELECT foo(1);
-- Expected output: ERROR: syntax error at or near "FUNCTION"

WITH
  FUNCTION broken(n NUMBER) RETURN NUMBER AS
  BEGIN
    RETRUN n;  -- typo
  END;
SELECT broken(1) FROM dual;
-- Expected output: ERROR: syntax error at or near "RETRUN"

WITH
  FUNCTION get_rows(n NUMBER) RETURN NUMBER AS
  BEGIN RETURN n; END;
SELECT * FROM get_rows(5);
-- Expected output: ERROR: WITH clause function cannot be used as a table or set-returning function

WITH
  FUNCTION public.qual_func(n NUMBER) RETURN NUMBER IS
  BEGIN RETURN n; END;
SELECT qual_func(1) FROM dual;
-- Expected output: ERROR: qualified name is not allowed in WITH FUNCTION declaration

EXPLAIN WITH
  FUNCTION add_one(n NUMBER) RETURN NUMBER AS BEGIN RETURN n + 1; END;
SELECT add_one(5) FROM dual;
-- Expected output includes: WITH Function: add_one(number) RETURN number

EXPLAIN (VERBOSE ON) WITH
  FUNCTION double(n NUMBER) RETURN NUMBER AS
  BEGIN RETURN n * 2; END;
SELECT double(3) FROM dual;
-- Expected output includes: Body: BEGIN RETURN n * 2; END