SQL FUNCTION

What is SQL FUNCTION?

Creates a reusable user defined function that returns a value.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FUNCTION: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Snowflake, BigQuery, Redshift, DB2, SAP HANA, SQLite (via extensions).

SQL FUNCTION Full Explanation

SQL FUNCTION (more formally the CREATE FUNCTION statement) lets you define a named, parameter-driven routine that can be called inside queries just like a built-in function. A function must return exactly one value (scalar, table, or composite depending on the dialect) and is typically used to encapsulate business logic, complex expressions, or repeated calculations.Unlike procedures, functions can be embedded in SELECT, WHERE, JOIN, and ORDER BY clauses because they are expected to be free of side-effects. Many engines enforce this by restricting data-modifying commands inside a function or by requiring the function to be declared as VOLATILE, STABLE, or IMMUTABLE (PostgreSQL) or by using the CONTAINS SQL / READS SQL DATA modifiers (MySQL, SQL-99).Functions are stored in the database catalog, versioned alongside schema changes, and executed on the server, which improves performance and security compared with shipping raw SQL to the client. You can overload functions by argument list, set default values for parameters, and call them from other functions or procedures.Caveats:- Function bodies are parsed only at creation time; syntax errors show up then, not at call time.- Some engines prevent data modification within a function, others allow it but mark the function as non-deterministic which can disable certain optimizations.- Dropping or replacing a function that is referenced by views or other routines can break dependent objects.

SQL FUNCTION Syntax

CREATE FUNCTION function_name ( [ mode ] param_name data_type [, ...] )
RETURNS return_data_type
[ LANGUAGE language_name ]
[ DETERMINISTIC | NOT DETERMINISTIC ]
[ { CONTAINS | NO } SQL | READS SQL DATA | MODIFIES SQL DATA ]
[ SECURITY DEFINER | SECURITY INVOKER ]
AS $$
  -- function body
  RETURN expression;
$$;

SQL FUNCTION Parameters

function_name (identifier) - Name of the function to create.
mode (keyword) - Optional IN, OUT, or INOUT direction of a parameter (dialect specific).
param_name (identifier) - Name of an input or output parameter.
data_type (type) - SQL data type of the parameter.
return_data_type (type) - SQL data type of the value returned by the function.
language_name (identifier) - Implementation language (SQL, PLPGSQL, TSQL, JavaScript, etc.).
DETERMINISTIC flag - Declares whether the function always returns the same output for the same inputs.
SQL data access modifiers - Declare whether the function reads or writes data.
SECURITY DEFINER/INVOKER - Defines whether the function runs with the privileges of its creator or caller.

Example Queries Using SQL FUNCTION

-- PostgreSQL example
CREATE FUNCTION get_discount(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * 0.9;
END;
$$ LANGUAGE plpgsql;

SELECT get_discount(100) AS discounted_price;

-- MySQL example
DELIMITER //
CREATE FUNCTION full_name(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
RETURN CONCAT(first_name, ' ', last_name);//
DELIMITER ;

SELECT full_name('Ada','Lovelace') AS scientist;

Expected Output Using SQL FUNCTION

Function is stored in the database schema
get_discount(100) returns 90
full_name('Ada','Lovelace') returns "Ada Lovelace"

Use Cases with SQL FUNCTION

Encapsulate complex tax or discount calculations so they can be reused in multiple queries.
Provide reusable text processing helpers such as proper_case or slugify.
Return small lookup tables (table valued functions) to simplify joins.
Abstract sensitive logic and expose only a safe function interface to analysts.
Facilitate code reuse in reporting queries and OLAP workloads.

Common Mistakes with SQL FUNCTION

Forgetting the RETURNS clause, which causes a syntax error.
Using data-modifying statements in a function declared as DETERMINISTIC or READS SQL DATA only, leading to runtime errors.
Omitting LANGUAGE in PostgreSQL, defaulting to SQL instead of PLPGSQL.
Assuming a function is immutable when it references volatile expressions like NOW().
Dropping a function that is still referenced by views or triggers.

First Introduced In

SQL:1999

Frequently Asked Questions

What is the difference between a SQL function and a stored procedure?

A function must return a value and is intended for use in SELECT or other expressions, while a procedure can return zero or many result sets and is usually executed with CALL/EXEC for side effects.

Can I create a function without specifying LANGUAGE?

In some engines yes, but PostgreSQL requires LANGUAGE. Omitting it defaults to LANGUAGE SQL, which may not support procedural constructs like IF or LOOP.

How do I replace an existing function?

Most databases support CREATE OR REPLACE FUNCTION. If not, you must DROP FUNCTION first and then CREATE FUNCTION again.

Do functions hurt query performance?

Well-written deterministic functions are often inlined or cached by the optimizer, so performance impact is minimal. Non-deterministic or I/O heavy functions can slow queries.