External functions
{CREATE [OR ALTER] | RECREATE} FUNCTION funcname [(<inparam> [, <inparam> ...])] RETURNS <type> [COLLATE collation] [DETERMINISTIC] EXTERNAL NAME <extname> ENGINE <engine> [AS <extbody>] <inparam> ::= <param_decl> [{= | DEFAULT} <value>] <value> ::= {literal | NULL | context_var} <param_decl> ::= paramname <type> [NOT NULL] [COLLATE collation] <extname> ::= '<module name>!<routine name>[!<misc info>]' <type> ::= <datatype> | [TYPE OF] domain | TYPE OF COLUMN rel.col <datatype> ::= {SMALLINT | INT[EGER] | BIGINT} | BOOLEAN | {FLOAT | DOUBLE PRECISION} | {DATE | TIME | TIMESTAMP} | {DECIMAL | NUMERIC} [(precision [, scale])] | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)] [CHARACTER SET charset] | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(size)] | BLOB [SUB_TYPE {subtype_num | subtype_name}] [SEGMENT SIZE seglen] [CHARACTER SET charset] | BLOB [(seglen [, subtype_num])]
All parameters of an external function can be changed using the ALTER statementFUNCTION
.
ALTER FUNCTION funcname [(<inparam> [, <inparam> ...])] RETURNS <type> [COLLATE collation] [DETERMINISTIC] EXTERNAL NAME <extname> ENGINE <engine> [AS <extbody>] <extname> ::= '<module name>!<routine name>[!<misc info>]'
You can remove an external function using the DROP FUNCTION statement.
DROP FUNCTION funcname
Parameter | Description |
---|---|
funcname |
Name of the stored function. Can contain up to 31 bytes. |
inparam |
Description of the input parameter. |
module name |
Name of the external module where the function resides. |
routine name |
The internal name of the function inside the external module. |
misc info |
User-defined information to pass to the functionexternal module. |
engine |
Name of the engine to use external functions. Usuallyspecifies the name of the UDR. |
extbody |
External function body. A string literal that canbe used by UDR for various purposes. |
Here we will not describe the syntax of the input parameters and the outputresult. It fully corresponds to the syntax for regular PSQL functions, whichis described in detail in the SQL Language Manual. Instead, we give examplesof declaring external functions with explanations.
create function sum_args (
n1 integer,
n2 integer,
n3 integer
)
returns integer
external name 'udrcpp_example!sum_args'
engine udr;
The implementation of the function is in the udrcpp_example
module. Within this module, the function is registered under the name sum_args
. The UDR engine is used to operate the external function.
create or alter function regex_replace (
regex varchar(60),
str varchar(60),
replacement varchar(60)
)
returns varchar(60)
external name 'org.firebirdsql.fbjava.examples.fbjava_example.FbRegex.replace(
String, String, String)'
engine java;
The implementation of the function is in the udrcpp_example
module. Withinthis module, the function is registered under the name sum_args
. The UDRengine is used to operate the external function.