External Procedures
{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.
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.
DROP PROCEDURE procname
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. |