by Helen Borrie
One of the PSQL treasures coming with Firebird 3 is the ability to write scalar functions in procedural SQL. Write them, commit them and they'll be declared to your database just as though they were external functions (UDFs). They have been launched as "stored functions", to distinguish them from "internal functions" (the ones that are built in to Firebird's SQL language) and "external functions", those that are loaded from an external library on demand, after being declared to the database.
The syntax will be familiar if you've written any stored procedures: it is very similar, although not exactly the same. There's a header, where the inputs and the output are declared, along with any local variables. The action happens inside a BEGIN...END block and the keyword RETURN sends the result back to the caller. Here is the syntax, slightly simplified:
{CREATE [OR ALTER] | ALTER | RECREATE} FUNCTION <name> [(param1 [, ...])]
RETURNS <type> AS
[DECLARE VARIABLE | FUNCTION] ... ;]
BEGIN
... END
Suppose you need a function that takes a date as an argument and returns a pretty string with the month fully spelt out, e.g., '25 December, 2014'. We have this simple little script for isql, named 'func_stringdate.txt':
create function stringdate (d date) returns
varchar(19) -- unnamed return value
as
declare xx smallint;
declare mm varchar(9);
declare yy smallint;
declare DSTRING varchar(19);
begin
DSTRING = '';
DSTRING = DSTRING || extract (day from d) || ' ';
xx = extract (month from d);
if (xx = 1) then mm = 'January';
else if (xx = 2) then mm = 'February';
else if (xx = 3) then mm = 'March';
else if (xx = 4) then mm = 'April';
else if (xx = 5) then mm = 'May';
else if (xx = 6) then mm = 'June';
else if (xx = 7) then mm = 'July';
else if (xx = 8) then mm = 'August';
else if (xx = 9) then mm = 'September';
else if (xx = 10) then mm = 'October';
else if (xx = 11) then mm = 'November';
else mm = 'December';
DSTRING = DSTRING || mm || ', ' || extract (year from d);
return DSTRING;
end ^
Here we go:
Database: dev:employee, User: sysdba SQL> set term ^; SQL> input c:\sql\func_stringdate.txt^ SQL> commit^ SQL> show functions^ Global functions: Function Name Invalid Dependency, Type ================================ ======= ===================================== STRINGDATE Packaged functions: Function Name Invalid Dependency, Type ================================ ======= =====================================
-- so, there's our PSQL function, right there in RDB$FUNCTIONS, where the UDF declarations would be, if there were any.
SQL> select stringdate(date '25.12.2014') from rdb$database^ STRINGDATE =================== 25 December, 2014 SQL> select full_name, stringdate(hire_date) as hired from employee^ FULL_NAME HIRED ===================================== =================== Nelson, Robert 28 December, 1988 Young, Bruce 28 December, 1988 Lambert, Kim 6 February, 1989 Johnson, Leslie 5 April, 1989 Forest, Phil 17 April, 1989 Weston, K. J. 17 January, 1990 Lee, Terri 1 May, 1990 Hall, Stewart 4 June, 1990 Young, Katherine 14 June, 1990 Papadopoulos, Chris 1 January, 1990
..... and so on.
SQL> drop function stringdate^ SQL> show functions^ There are no user-defined functions in this database SQL>
That was easy! But Firebird 3 has a few more neat tricks. It will let us run subroutines inside both stored functions and stored procedures. That's what this syntax line was about:
[DECLARE VARIABLE | FUNCTION] ... ;]
In the first function, we declared some local variables. For the next one, we declare a sub-function as well and achieve the same end by slightly different means:
create function stringdate1 (d date) returns varchar(19)
as
declare xx smallint;
declare mm varchar(9);
declare yy smallint;
declare DSTRING varchar(19);
-- we declare and code the sub-function together, right here:
declare function mstring (xx integer) returns varchar(9)
as
begin
if (xx = 1) then return 'January';
else if (xx = 2) then return 'February';
else if (xx = 3) then return 'March';
else if (xx = 4) then return 'April';
else if (xx = 5) then return 'May';
else if (xx = 6) then return 'June';
else if (xx = 7) then return 'July';
else if (xx = 8) then return 'August';
else if (xx = 9) then return 'September';
else if (xx = 10) then return 'October';
else if (xx = 11) then return 'November';
else return 'December';
end
-- Here's where we implement the main function
-- and call the sub-function
begin
DSTRING = '';
DSTRING = DSTRING || extract (day from d) || ' ' || mstring(extract (month from d)) || ', ' || extract (year from d);
return DSTRING;
end ^
Here we go again:
SQL> input c:\sql\func_stringdate1.txt^ SQL> commit^ SQL> show functions^ Global functions: Function Name Invalid Dependency, Type ================================ ======= ===================================== STRINGDATE1 Packaged functions: Function Name Invalid Dependency, Type ================================ ======= =====================================
Soo, there's our PSQL function, right there in RDB$FUNCTIONS, where the externally-loaded UDF declarations would be, if there were any.
SQL> select stringdate1(date '1960-04-21') from rdb$database^ STRINGDATE1 =================== 21 April, 1960 SQL> select full_name, stringdate1(hire_date) as hired from employee^ FULL_NAME HIRED ===================================== =================== Nelson, Robert 28 December, 1988 Young, Bruce 28 December, 1988 Lambert, Kim 6 February, 1989 Johnson, Leslie 5 April, 1989 Forest, Phil 17 April, 1989 Weston, K. J. 17 January, 1990 ... SQL>
As you can see, your user-defined stored functions can be used anywhere you would use a built-in or external scalar function. The core team is already referring to external functions as "legacy UDFs"!