FirebirdSQL logo

External functions

Syntax
{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.

Syntax
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.

Syntax
DROP FUNCTION funcname
Table 1. Some parameters of the external function
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.

External Procedures

Syntax
{CREATE [OR ALTER] | RECREATE} PROCEDURE procname [(<inparam> [, <inparam> ...])]
RETURNS (<outparam> [, <outparam> ...])
EXTERNAL NAME <extname> ENGINE <engine>
[AS <extbody>]

<inparam> ::= <param_decl> [{= | DEFAULT} <value>]

<outparam>  ::=  <param_decl>

<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 procedure can be changed using the ALTER PROCEDURE statement.

Syntax
ALTER PROCEDURE procname [(<inparam> [, <inparam> ...])]
RETURNS (<outparam> [, <outparam> ...])
EXTERNAL NAME <extname> ENGINE <engine>
[AS <extbody>]

You can drop an external procedure using the DROP PROCEDURE statement.

Syntax
DROP PROCEDURE procname
Table 1. Some parameters of the external procedure
Parameter Description

procname

Name of the stored procedure. Can contain up to 31 bytes.

inparam

Description of the input parameter.

outparam

Description of the output parameter.

module name

The name of the external module in which the procedure resides.

routine name

Internal name of the procedure inside the external module.

misc info

User-defined information to pass toexternal module procedure.

engine

Name of the engine to use external procedures. Usuallyspecifies the name of the UDR.

extbody

The body of the external procedure. A string literal that canbe used by UDR for various purposes.

Here we will not describe the syntax of input and output parameters. It isfully consistent with the syntax for regular PSQL procedures, which isdescribed in detail in the SQL Language Manual. Instead, let’s takeexamples of declaration of external procedures with explanations.

create procedure gen_rows_pascal (
    start_n integer not null,
    end_n integer not null
)
returns (
    result integer not null
)
external name 'pascaludr!gen_rows'
engine udr;

The implementation of the function is in the pascaludr module. Within thismodule, the procedure is registered under the name gen_rows. The UDR engine iis used to run the external procedure.

create or alter procedure write_log (
  message varchar(100)
)
external name 'pascaludr!write_log'
engine udr;

The implementation of the function is in the pascaludr module. Within thismodule, the procedure is registered under the name write_log. The UDR engineis used to run the external procedure.

create or alter procedure employee_pgsql (
  -- Firebird 3.0.0 has a bug with external procedures without parameters
  dummy integer = 1
)
returns (
  id type of column employee.id,
  name type of column employee.name
)
external name 'org.firebirdsql.fbjava.examples.fbjava_example.FbJdbc
    .executeQuery()!jdbc:postgresql:employee|postgres|postgres'
engine java
as 'select * from employee';

The implementation of the function is in the static function executeQuery of the classorg.firebirdsql.fbjava.examples.fbjava_example.FbJdbc. Afterexclamation mark "!" contains information for connecting to an externaldatabase via JDBC. The Java engine is used to run the external function. Here,as the "body" of the external procedure, an SQL query is passed to retrievedata.

Note
Comment

This procedure uses a stub that passesunused parameter. This is due to the fact that in Firebird 3.0there is a bug with the processing of external procedures without parameters.

Placing External Procedures and Functions Inside Packages

A group of related procedures and functions is conveniently placed in PSQLpackages. The packages can contain both external and conventionalpsql procedures and functions.

Syntax
{CREATE [OR ALTER] | RECREATE} PACKAGE package_name
AS
BEGIN
  [<package_item> ...]
END

{CREATE | RECREATE} PACKAGE BODY package_name
AS
BEGIN
  [<package_item> ...]
  [<package_body_item> ...]
END

<package_item> ::=
    <function_decl>;
  | <procedure_decl>;

<function_decl> ::=
  FUNCTION func_name [(<in_params>)]
  RETURNS <type> [COLLATE collation]
  [DETERMINISTIC]

<procedure_decl> ::=
  PROCEDURE proc_name [(<in_params>)]
  [RETURNS (<out_params>)]

<package_body_item> ::=
    <function_impl>
  | <procedure_impl>

<function_impl> ::=
  FUNCTION func_name [(<in_impl_params>)]
  RETURNS <type> [COLLATE collation]
  [DETERMINISTIC]
  <routine body>

<procedure_impl> ::=
  PROCEDURE proc_name [(<in_impl_params>)]
  [RETURNS (<out_params>)]
  <routine body>

<routine body> ::= <sql routine body> | <external body reference>

<sql routine body> ::=
  AS
    [<declarations>]
  BEGIN
    [<PSQL_statements>]
  END

<declarations> ::= <declare_item> [<declare_item> ...]

<declare_item> ::=
    <declare_var>;
  | <declare_cursor>;
  | <subroutine declaration>;
  | <subroutine implimentation>

<subroutine declaration> ::= <subfunc_decl> | <subproc_decl>

<subroutine implimentation> ::= <subfunc_impl> | <subproc_impl>

<external body reference> ::=
  EXTERNAL NAME <extname> ENGINE <engine> [AS <extbody>]

<extname> ::= '<module name>!<routine name>[!<misc info>]'

For external procedures and functions, the package header specifies the name, inputparameters, their types, default values, and output parameters, and in the body of thepackage everything is the same, except for the default values, as well as the locationin the external module (clause EXTERNAL NAME), the name of the engine, and possiblythe "body" of the procedure/function.

Let’s say you wrote a UDR to work with regular expressions,which is located in anexternal module (dynamic library) PCRE, and you have several other UDRs that performother tasks. If we did not use PSQL packages, then all our external procedures andwould be intermingled both with each other and with regular PSQL procedures andfunctions. This makes it difficult to find dependencies and make changes to externalmodules, and also creates confusion, and forces at least the use of prefixes to groupprocedures and functions.PSQL packages make this task much easier for us.

SET TERM ^;

CREATE OR ALTER PACKAGE REGEXP
AS
BEGIN
  PROCEDURE preg_match(
      APattern VARCHAR(8192), ASubject VARCHAR(8192))
    RETURNS (Matches VARCHAR(8192));

  FUNCTION preg_is_match(
      APattern VARCHAR(8192), ASubject VARCHAR(8192))
    RETURNS BOOLEAN;

  FUNCTION preg_replace(
      APattern VARCHAR(8192),
      AReplacement VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS VARCHAR(8192);

  PROCEDURE preg_split(
      APattern VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS (Lines VARCHAR(8192));

  FUNCTION preg_quote(
      AStr VARCHAR(8192),
      ADelimiter CHAR(10) DEFAULT NULL)
    RETURNS VARCHAR(8192);
END^

RECREATE PACKAGE BODY REGEXP
AS
BEGIN
  PROCEDURE preg_match(
      APattern VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS (Matches VARCHAR(8192))
    EXTERNAL NAME 'PCRE!preg_match' ENGINE UDR;

  FUNCTION preg_is_match(
      APattern VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS BOOLEAN
  AS
  BEGIN
    RETURN EXISTS(
      SELECT * FROM preg_match(:APattern, :ASubject));
  END

  FUNCTION preg_replace(
      APattern VARCHAR(8192),
      AReplacement VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS VARCHAR(8192)
    EXTERNAL NAME 'PCRE!preg_replace' ENGINE UDR;

  PROCEDURE preg_split(
      APattern VARCHAR(8192),
      ASubject VARCHAR(8192))
    RETURNS (Lines VARCHAR(8192))
    EXTERNAL NAME 'PCRE!preg_split' ENGINE UDR;

  FUNCTION preg_quote(
      AStr VARCHAR(8192),
      ADelimiter CHAR(10))
    RETURNS VARCHAR(8192)
    EXTERNAL NAME 'PCRE!preg_quote' ENGINE UDR;
END^

SET TERM ;^

External triggers

Syntax
{CREATE [OR ALTER] | RECREATE} TRIGGER trigname
{
    <relation_trigger_legacy>
  | <relation_trigger_sql2003>
  | <database_trigger>
  | <ddl_trigger>
}
<external-body>

<external-body> ::=
  EXTERNAL NAME <extname> ENGINE <engine>
  [AS <extbody>]

<relation_trigger_legacy> ::=
  FOR {tablename | viewname}
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <mutation_list>
  [POSITION number]

<relation_trigger_sql2003> ::=
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <mutation_list>
  [POSITION number]
  ON {tablename | viewname}

<database_trigger> ::=
  [ACTIVE | INACTIVE]
  ON db_event
  [POSITION number]

<ddl_trigger> ::=
  [ACTIVE | INACTIVE]
  {BEFORE | AFTER} <ddl_events>
  [POSITION number]

<mutation_list> ::= <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::= INSERT | UPDATE | DELETE

<db_event> ::=
    CONNECT
  | DISCONNECT
  | TRANSACTION START
  | TRANSACTION COMMIT
  | TRANSACTION ROLLBACK


<ddl_events> ::=
    ANY DDL STATEMENT
  | <ddl_event_item> [{OR <ddl_event_item>} ...]

<ddl_event_item> ::=
    CREATE TABLE | ALTER TABLE | DROP TABLE
  | CREATE PROCEDURE | ALTER PROCEDURE | DROP PROCEDURE
  | CREATE FUNCTION | ALTER FUNCTION | DROP FUNCTION
  | CREATE TRIGGER | ALTER TRIGGER | DROP TRIGGER
  | CREATE EXCEPTION | ALTER EXCEPTION | DROP EXCEPTION
  | CREATE VIEW | ALTER VIEW | DROP VIEW
  | CREATE DOMAIN | ALTER DOMAIN | DROP DOMAIN
  | CREATE ROLE | ALTER ROLE | DROP ROLE
  | CREATE SEQUENCE | ALTER SEQUENCE | DROP SEQUENCE
  | CREATE USER | ALTER USER | DROP USER
  | CREATE INDEX | ALTER INDEX | DROP INDEX
  | CREATE COLLATION | DROP COLLATION
  | ALTER CHARACTER SET
  | CREATE PACKAGE | ALTER PACKAGE | DROP PACKAGE
  | CREATE PACKAGE BODY | DROP PACKAGE BODY
  | CREATE MAPPING | ALTER MAPPING | DROP MAPPING

An external trigger can be changed with the ALTER TRIGGER statement.

Syntax
ALTER TRIGGER trigname {
[ACTIVE | INACTIVE]
[
    {BEFORE | AFTER} {<mutation_list> | <ddl_events>}
  | ON db_event
]
[POSITION number]
[<external-body>]

<external-body> ::=
  EXTERNAL NAME <extname> ENGINE <engine>
  [AS <extbody>]

<extname> ::= '<module name>!<routine name>[!<misc info>]'

<mutation_list> ::= <mutation> [OR <mutation> [OR <mutation>]]

<mutation> ::= { INSERT | UPDATE | DELETE }

You can remove an external trigger using the DROP TRIGGER statement.

Syntax
DROP TRIGGER trigname
Table 1. Some external trigger parameters
Parameter Description

trigname

Trigger name. Can contain up to 31 bytes.

relation_trigger_legacy

Table trigger declaration(inherited).

relation_trigger_sql2003

Table trigger declaration according toSQL-2003 standard.

database_trigger

Declaration of a database trigger.

ddl_trigger

DDL trigger declaration.

tablename

Table name.

viewname

The name of the view.

mutation_list

List of table events.

mutation

One of the table events.

db_event

Connection or transaction event.

ddl_events

List of metadata change events.

ddl_event_item

One of the metadata change events.

number

The order in which the trigger fires. From 0 to 32767.

extbody

External trigger body. A string literal that canbe used by UDR for various purposes.

module name

Name of the external module where the trigger is located.

routine name

Internal name of the trigger inside the external module.

misc info

User-defined information to pass to the triggerexternal module.

engine

Name of the engine to use external triggers. Usuallyspecifies the name of the UDR.

Here are examples of declaring external triggers with explanations.

create database 'c:\temp\slave.fdb';

create table persons (
    id integer not null,
    name varchar(60) not null,
    address varchar(60),
    info blob sub_type text
);

commit;

create database 'c:\temp\master.fdb';

create table persons (
    id integer not null,
    name varchar(60) not null,
    address varchar(60),
    info blob sub_type text
);

create table replicate_config (
    name varchar(31) not null,
    data_source varchar(255) not null
);

insert into replicate_config (name, data_source)
   values ('ds1', 'c:\temp\slave.fdb');

create trigger persons_replicate
after insert on persons
external name 'udrcpp_example!replicate!ds1'
engine udr;

The trigger implementation is in the udrcpp_example module. Within this module, thetrigger is registered under the name replicate. The UDR engine is used to operate theexternal trigger.

The link to the external module uses an additional parameter ds1, according to which,inside the external trigger, the configuration for connecting to the external databaseis read from the replicate_config table.