Can my DB?

CREATE OR REPLACE FUNCTION add_tax(price NUMERIC, rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN price * (1 + rate);
END;
$$;

SELECT add_tax(100.00, 0.08);

• Supports LANGUAGE sql, plpgsql, plpython3u, plperl, and C among others.
• Functions can be classified IMMUTABLE, STABLE, or VOLATILE for query optimization.
• SECURITY DEFINER option allows functions to run with the privileges of the defining user.
• Return types include scalars, composite types, and SETOF (table-returning).

CREATE FUNCTION add_tax(price DECIMAL(10,2), rate DECIMAL(5,4))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * (1 + rate);
END;

SELECT add_tax(100.00, 0.08);

• Only returns scalar values; no external language support.
• DETERMINISTIC or NOT DETERMINISTIC declaration required for replication safety.
• Functions stored in the mysql.proc table (5.x) or information_schema (8.x).

CREATE FUNCTION add_tax(price DECIMAL(10,2), rate DECIMAL(5,4))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN price * (1 + rate);
END;

SELECT add_tax(100.00, 0.08);

• Compatible with MySQL UDF syntax.
• Also supports PL/SQL-style blocks for Oracle compatibility mode.
• Oracle compatibility mode (sql_mode=ORACLE) enables additional syntax.

CREATE OR REPLACE FUNCTION add_tax(
  p_price  NUMBER,
  p_rate   NUMBER
) RETURN NUMBER AS
BEGIN
  RETURN p_price * (1 + p_rate);
END;
/

SELECT add_tax(100, 0.08) FROM DUAL;

• PL/SQL functions use RETURN keyword (no S) in the signature.
• Supports pipelined functions (PIPELINED keyword) that return rows incrementally.
• AUTHID CURRENT_USER or AUTHID DEFINER controls privilege context.
• Functions can be defined within packages for better organization and overloading.

CREATE FUNCTION dbo.add_tax(
  @price DECIMAL(10,2),
  @rate  DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS BEGIN
  RETURN @price * (1 + @rate);
END;

SELECT dbo.add_tax(100.00, 0.08);

• T-SQL scalar functions must be called with schema prefix (e.g., dbo.fn_name()).
• Also supports scalar CLR (C#/VB.NET) functions via assemblies.
• Scalar UDF Inlining (SQL Server 2019+) automatically inlines simple UDFs for better performance.
• Schema binding (WITH SCHEMABINDING) prevents underlying objects from being dropped.

-- Python example using sqlite3 module
import sqlite3

def add_tax(price, rate):
    return price * (1 + rate)

conn = sqlite3.connect(':memory:')
conn.create_function('add_tax', 2, add_tax)
cursor = conn.execute('SELECT add_tax(100.0, 0.08)')
print(cursor.fetchone()[0])  -- 108.0

• CREATE FUNCTION SQL syntax does not exist in SQLite.
• Functions must be registered via the SQLite C API (sqlite3_create_function) or language-specific wrappers.
• Python: sqlite3.create_function(); Node.js: better-sqlite3 db.function(); Go: go-sqlite3 RegisterFunc.
• This provides custom query-callable functions, but not SQL-defined UDFs in the usual database sense.

-- SQL UDF
CREATE FUNCTION add_tax(price FLOAT, rate FLOAT)
RETURNS FLOAT
STABLE
AS $$
  SELECT price * (1 + rate)
$$ LANGUAGE sql;

-- Python UDF
CREATE FUNCTION py_add_tax(price FLOAT, rate FLOAT)
RETURNS FLOAT
STABLE
AS $$
  return price * (1 + rate)
$$ LANGUAGE plpythonu;

SELECT add_tax(100.0, 0.08);

• Supports SQL and Python (plpythonu) scalar UDFs.
• Python UDFs can import standard library modules.
• UDFs must be declared STABLE or IMMUTABLE (VOLATILE not allowed for UDFs).
• Scalar UDFs are limited to returning a single scalar value.

CREATE FUNCTION add_tax(price DECIMAL(10,2), rate DECIMAL(5,4))
RETURNS DECIMAL(10,2)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN
  RETURN price * (1 + rate);
END;

SELECT add_tax(100.00, 0.08) FROM SYSIBM.SYSDUMMY1;

• Supports LANGUAGE SQL, LANGUAGE JAVA, and LANGUAGE C for external functions.
• SQL PL functions use BEGIN...END blocks with full procedural logic.
• DETERMINISTIC and NO EXTERNAL ACTION declarations affect optimization.
• External Java functions require a JAR registered with SQLJ.INSTALL_JAR.

-- SQL UDF
CREATE FUNCTION add_tax(price FLOAT, rate FLOAT)
RETURNS FLOAT
AS $$
  price * (1 + rate)
$$;

-- JavaScript UDF
CREATE FUNCTION js_add_tax(price FLOAT, rate FLOAT)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS $$
  return PRICE * (1 + RATE);
$$;

SELECT add_tax(100.0, 0.08);

• Supports LANGUAGE SQL, JAVASCRIPT, PYTHON, JAVA, and SCALA.
• JavaScript was the original UDF language; Python UDFs (Snowpark) added later.
• Java and Scala UDFs require a handler class within an inline or staged code block.
• SQL UDFs are single expressions; JavaScript and Python support multi-statement logic.

-- SQL UDF
CREATE FUNCTION my_dataset.add_tax(price FLOAT64, rate FLOAT64)
AS (price * (1 + rate));

-- JavaScript UDF
CREATE FUNCTION my_dataset.js_add_tax(price FLOAT64, rate FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  return price * (1 + rate);
""";

SELECT my_dataset.add_tax(100.0, 0.08);

• SQL UDFs are inlined and treated as expressions; no BEGIN/END block.
• JavaScript UDFs run in a sandboxed V8 environment.
• UDFs are scoped to a dataset and can be shared across projects.
• Temporary UDFs can be defined in the same query using CREATE TEMP FUNCTION.