Who Can Create a Function
The CREATE FUNCTION
statement can be executed by:
-
Users with the
CREATE FUNCTION
privilege
The user who created the stored function becomes its owner.
The CREATE FUNCTION
statement can be executed by:
Users with the CREATE FUNCTION
privilege
The user who created the stored function becomes its owner.
CREATE FUNCTION
ExamplesCreating a stored function
CREATE FUNCTION ADD_INT (A INT, B INT DEFAULT 0)
RETURNS INT
AS
BEGIN
RETURN A + B;
END
Calling in a select:
SELECT ADD_INT(2, 3) AS R FROM RDB$DATABASE
Call inside PSQL code, the second optional parameter is not specified:
MY_VAR = ADD_INT(A);
Creating a deterministic stored function
CREATE FUNCTION FN_E()
RETURNS DOUBLE PRECISION DETERMINISTIC
AS
BEGIN
RETURN EXP(1);
END
Creating a stored function with table column type parameters
Returns the name of a type by field name and value
CREATE FUNCTION GET_MNEMONIC (
AFIELD_NAME TYPE OF COLUMN RDB$TYPES.RDB$FIELD_NAME,
ATYPE TYPE OF COLUMN RDB$TYPES.RDB$TYPE)
RETURNS TYPE OF COLUMN RDB$TYPES.RDB$TYPE_NAME
AS
BEGIN
RETURN (SELECT RDB$TYPE_NAME
FROM RDB$TYPES
WHERE RDB$FIELD_NAME = :AFIELD_NAME
AND RDB$TYPE = :ATYPE);
END
Creating an external stored function
Create a function located in an external module (UDR).Function implementation is located in the external module udrcpp_example
.The name of the function inside the module is wait_event
.
CREATE FUNCTION wait_event (
event_name varchar (31) CHARACTER SET ascii
) RETURNS INTEGER
EXTERNAL NAME 'udrcpp_example!Wait_event'
ENGINE udr
Creating a stored function containing a sub-function
Creating a function to convert a number to hexadecimal format.
CREATE FUNCTION INT_TO_HEX (
ANumber BIGINT ,
AByte_Per_Number SMALLINT = 8)
RETURNS CHAR (66)
AS
DECLARE VARIABLE xMod SMALLINT ;
DECLARE VARIABLE xResult VARCHAR (64);
DECLARE FUNCTION TO_HEX (ANum SMALLINT ) RETURNS CHAR
AS
BEGIN
RETURN CASE ANum
WHEN 0 THEN '0'
WHEN 1 THEN '1'
WHEN 2 THEN '2'
WHEN 3 THEN '3'
WHEN 4 THEN '4'
WHEN 5 THEN '5'
WHEN 6 THEN '6'
WHEN 7 THEN '7'
WHEN 8 THEN '8'
WHEN 9 THEN '9'
WHEN 10 THEN 'A'
WHEN 11 THEN 'B'
WHEN 12 THEN 'C'
WHEN 13 THEN 'D'
WHEN 14 THEN 'E'
WHEN 15 THEN 'F'
ELSE NULL
END;
END
BEGIN
xMod = MOD (ANumber, 16);
ANumber = ANumber / 16;
xResult = TO_HEX (xMod);
WHILE (ANUMBER> 0) DO
BEGIN
xMod = MOD (ANumber, 16);
ANumber = ANumber / 16;
xResult = TO_HEX (xMod) || xResult;
END
RETURN '0x' || LPAD (xResult, AByte_Per_Number * 2, '0' );
END
With DEFINER
set for function f
, user US
needs only the EXECUTE
privilege on f
.If it were set for INVOKER
, the user would also need the INSERT
privilege on table t
.
set term ^;
create function f (i integer) returns int SQL SECURITY DEFINER
as
begin
insert into t values (:i);
return i + 1;
end^
set term ;^
grant execute on function f to user us;
commit;
connect 'localhost:/tmp/59.fdb' user us password 'pas';
select f(3) from rdb$database;