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 
CREATE OR REPLACE my_function
(p_contract IN VARCHAR2, p_org_id IN VARCHAR2)
RETURN DATE
AS
l_ret_eff_date date;

if ....

return l_ret_eff_date;

SELECT my_function('PARM1', 'ORG1') ....
MSSQL
Retrieved from "http://en.wikibooks.org/w/index.php?title=SQL_Dialects_Reference/Procedural_language/User-defined_functions&oldid=2771599"