Wikibooks

SQL Dialects Reference/Procedural language/User-defined functions

< SQL Dialects Reference

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(...) = ...
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
Retrieved from "https://en.wikibooks.org/w/index.php?title=SQL_Dialects_Reference/Procedural_language/User-defined_functions&oldid=4297880"