SQL Dialects Reference/Procedural language/User-defined functions

From Wikibooks, open books for an open world
< SQL Dialects Reference
Jump to: navigation, search

User-defined functions (UDF)[edit]

Database Create syntax Calling
DB2
CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type
BEGIN
   /* SQL code */
END
VALUES function_name(...)

or

SELECT function_name(...) FROM ... WHERE function_name(...) = ...
SQLite

N/A

N/A

MonetDB
CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type
BEGIN
   /* SQL code */
END
SELECT function_name(...) FROM ... WHERE function_name(...) = ...
MySQL
DELIMITER $$

CREATE FUNCTION function_name
  (input_parameter_name datatype, ... )
RETURNS datatype
BEGIN
RETURN
  /* SQL code */
END$$

DELIMITER ;
SELECT function_name(...)
PostgreSQL
CREATE FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS datatype
AS $$
DECLARE
  variable_name datatype;
BEGIN
  /* SQL code */
END;
$$ LANGUAGE plpgsql;
SELECT function_name(...)
Firebird

UDFs are written in external tools and compiled into executable form.
They are dynamically loaded at runtime.
In order for server to pick them up, they need to be registered, like this:

DECLARE EXTERNAL FUNCTION function_name [datatype, ...]
RETURNS datatype
ENTRY_POINT 'entryname'
MODULE_NAME 'modulename';
SELECT function_name(...)
OpenLink Virtuoso
Oracle
MSSQL