SQL Dialects Reference/Procedural language/User-defined functions

From Wikibooks, open books for an open world
Jump to navigation Jump to search

User-defined functions (UDF)[edit | edit source]

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(...) = ...
MonetDB
CREATE [ OR REPLACE ] FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type   /* return type can also be a table structure */
BEGIN
   /* SQL code */
END


CREATE [ OR REPLACE ] FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type   /* return type can also be a table structure */
LANGUAGE { C | CPP | R | PYTHON3 | PYTHON3_MAP }
{
   function_body_in_language_syntax
}


CREATE [ OR REPLACE ] FUNCTION function_name
  (input_parameter_name datatype, ...)
RETURNS return_type   /* return type can also be a table structure */
EXTERNAL NAME  MAL_function_name
SELECT function_name(...)

/* when the return type of the function is a table, following is allowed */
SELECT ... FROM 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 := SYSDATE;
BEGIN
  RETURN l_ret_eff_date;
END;
/
SELECT my_function('PARM1', 'ORG1')
FROM dual;
SQLite

N/A

N/A

SQL Server