LucidDbUdfHowto

From Eigenpedia

Write a UDF as a SQL macro

The simplest kind of user-defined function which can be added to LucidDB is basically just a strongly-typed macro written as a SQL expression. Here's how to create one (full syntax):

create schema goldilocks;
set schema 'goldilocks';
create function test_temperature(celsius double)
returns varchar(20)
contains sql
return case 
when celsius < 20.0 then 'Too cold!'
when celsius < 80.0 then 'Just right!'
else 'Too hot!' end;

Now, create some test data:

create table porridge(bowl_owner varchar(20), bowl_temperature double);
insert into porridge
values
    ('MB', 100.0),
    ('PB', 10.1),
    ('BB', 50.0);

Finally, invoke the UDF:

select
    bowl_owner, 
    goldilocks.test_temperature(bowl_temperature) as response
from porridge;

You should get these results:

0: jdbc:luciddb:> select
. . . . . . . . >     bowl_owner,
. . . . . . . . >     goldilocks.test_temperature(bowl_temperature) as response
. . . . . . . . > from porridge;
+-------------+--------------+
| BOWL_OWNER  |   RESPONSE   |
+-------------+--------------+
| MB          | Too hot!     |
| PB          | Too cold!    |
| BB          | Just right!  |
+-------------+--------------+

Note that the name resolution rules for UDF invocation lookup are not the same as the lookup rules for other objects like tables; that's why we had to qualify the test_temperature function by schema even though we didn't have to qualify the porridge table. SQL:2003 defines the concept of a path, which is very similar to the search path an operating system uses to find programs, except that in LucidDB the containers are schemas rather than folders/directories. Here's how to invoke the UDF without using a qualifier:

set path 'goldilocks';
select
    bowl_owner, 
    test_temperature(bowl_temperature) as response
from porridge;

SQL macros are very easy to write and install, but they are limited to what can be expressed in a single return statement; procedural logic and temporary variables are not supported. To learn how to write more sophisticated functions, see LucidDbUdfJavaHowto.