SQL LANGUAGE

What is SQL LANGUAGE in CREATE FUNCTION statements?

Specifies that the routine body is written in plain SQL rather than an external procedural language.

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 LANGUAGE: PostgreSQL, MySQL, MariaDB, Amazon Redshift (as SQL), IBM Db2 (SQL PL). Not required in SQL Server or Oracle.

SQL LANGUAGE Full Explanation

LANGUAGE SQL (often written as just LANGUAGE sql in PostgreSQL or LANGUAGE SQL in MySQL) is a clause used inside CREATE FUNCTION and CREATE PROCEDURE statements. It tells the database engine that the routineas logic consists solely of standard SQL statements, not of another procedural language such as PLpgSQL, PL/SQL, Java, or Python. When the clause is present, the server treats the routine as a set of one or more SQL commands executed in the context of the caller, inheriting the calleras transaction and privilege scope.Because SQL is the databaseas native language, routines declared with LANGUAGE SQL are usually parsed, optimized, and executed more efficiently than those written in higher-level procedural languages. However, they lack flow-control constructs beyond what is available in ANSI SQL (e.g., CASE, UNION, subqueries) and therefore suit simple computations, data transformations, and wrapper queries rather than complex business logic.Some systems supply implicit defaults. PostgreSQL defaults to LANGUAGE sql if no language is given and the body starts with SELECT, INSERT, UPDATE, or DELETE. MySQL requires an explicit LANGUAGE SQL keyword when you want to contrast it with LANGUAGE JAVA (deprecated) or other potential languages. Always check your dialectas documentation for exact requirements.

SQL LANGUAGE Syntax

-- PostgreSQL style
CREATE [OR REPLACE] FUNCTION function_name(arg_list)
RETURNS return_type
LANGUAGE sql
AS $$
  <SQL statements>
$$;

-- MySQL style
CREATE FUNCTION function_name(arg_list)
RETURNS return_type
LANGUAGE SQL
[characteristics]
BEGIN
  <SQL statements>;
END;

SQL LANGUAGE Parameters

language_name (identifier) - Name of the language; for this keyword the value must be SQL (case insensitive). No other parameters.

Example Queries Using SQL LANGUAGE

-- PostgreSQL example
CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE sql
AS $$
  SELECT a + b;
$$;

-- MySQL example
DELIMITER //
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
  RETURN a + b;
END //
DELIMITER ;

Expected Output Using SQL LANGUAGE

Both statements register a stored function called add_numbers that simply returns the sum of its two arguments
No result set is produced at creation time; the routine becomes callable by subsequent SQL statements

Use Cases with SQL LANGUAGE

Declare simple scalar or table functions that wrap one SELECT statement
Encapsulate frequently used joins and filters for reuse
Provide a stable API layer over schema details while keeping execution entirely in the SQL engine
Improve performance compared to procedural languages when only declarative SQL is necessary

Common Mistakes with SQL LANGUAGE

Omitting the LANGUAGE clause in dialects that do not default to SQL (causes syntax errors)
Misspelling LANGUAGE or using quotes around SQL (treated as string literal)
Attempting to include procedural constructs (IF, LOOP) not available in pure SQL
Forgetting to change DELIMITER in MySQL, leading to premature statement termination

First Introduced In

SQL/PSM (1996) and PostgreSQL 7.0

Frequently Asked Questions

What is LANGUAGE SQL used for?

It specifies that the body of a stored function or procedure is written entirely in standard SQL, allowing the database to run it without an external procedural interpreter.

Is LANGUAGE SQL the default in PostgreSQL?

Yes, if the function body contains a single SQL command. For multi-statement functions, you must still declare LANGUAGE sql explicitly.

When should I avoid LANGUAGE SQL?

Avoid it when you need procedural features such as loops, variable assignment, or exception handling. Choose PLpgSQL or another procedural language instead.

Does MySQL always require LANGUAGE SQL?

Yes. MySQL expects LANGUAGE SQL in CREATE FUNCTION to clarify that the routine uses the SQL language rather than other languages like JAVA.