CREATE FUNCTION

CREATE FUNCTION — create a user defined function (UDF)

Syntax

CREATE [OR REPLACE] FUNCTION [schema.]name
    [ ( arg_name arg_type [ ,arg_name arg_type ]... ) ]
    RETURNS
    {
        return_data_type
    |
        TABLE table_name ( column_name column_type [, column_name column_type ]... )
    }
    [ DETERMINISTIC | NOT DETERMINISTIC ]
    [ LANGUAGE SQL | LANGUAGE JAVA ]
    [ SECURITY INVOKER | SECURITY DEFINER ]
{
    EXTERNAL 'jar_id:method_name'
|
    AS
        sql_code_block
    END_FUNCTION;
}

Description

The CREATE FUNCTION statement is used to create user defined functions. There are two types of functions.

  • A scalar function returns a simple value. It can be used in the SELECT list of a SELECT query or a view. A scalar function can also be used in a WHERE clause.

  • A table function returns a table, that is, multiple rows with multiple values. It can be used in place of a database table object in a FROM clause.

When the OR REPLACE clause is used for an existing function name, the effect is that of dropping the existing function and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a function with the specified name does not already exist.

CREATE FUNCTION may also be used to create stored procedures written in Java. See Language below and Using Embedded Java Stored Procedures.

Only users assigned the system DBA role can create a function. For more information, see GRANT). If schema is provided, then the function is created in the specified schema. Otherwise, it is created in the current schema. The name of the function must not match any existing function with the same number of input arguments in the same schema. However, multiple functions with the same name can be defined in the same schema if they contain different numbers of arguments. This is called overloading, see Example 2.

The user that creates the function becomes the owner of the function. The owner must use the GRANT statement to give ALTER and EXECUTE privileges to other users in order for them to be able to change or execute the function. Any user that has permission to alter a function can also remove the function. If a user drops and recreates a function then the new function is not the same entity as the old function. hat user is the owner of the recreated function and must GRANT appropriate privileges to other users.

Functions are not case sensitive. For example, a function created with the name Fnc_ToDo can be called by select FNC_TODO() from dual.

When using CREATE OR REPLACE statements, and the same database object is created concurrently from two different transactions, the following error may be returned:
duplicate value in unique index ...
In the event that a user-defined function is called by an expression-based index, any attempt to drop or modify the function will complete with an error warning but the index will not be recreated.

Arguments

Each argument to a user defined function is implicitly declared as an input argument, and will not be modified by the UDF.

Return Values

The RETURNS clause is required. If a function returns a single data type value, it is a scalar function. If a function returns a table, it is a table function. A function defined as a table function will return multiple rows that contain multiple values.

A scalar function returns a single value. This can be used in a SELECT statement and/or this can be used in a WHERE clause.

A table function is used in the FROM clause of a SELECT statement. There are two ways to return the table, RETURN (SELECT…​) which creates a streaming result set that fetches the rows on demand and INSERT INTO…​ with a separate RETURN statement which will return the table once all the data is inserted. When using a table function, you cannot use the same function multiple times as items in the FROM clause, even if the parameter values being passed to the function are different.

Example 5 illustrates returning the table using RETURN (SELECT…​). There are times when a single SELECT statement cannot generate the result set. Example 6 illustrates returning the table using INSERT INTO…​ and a separate RETURN statement once all of the data is inserted. There can be one or more INSERT INTO statements prior to the RETURN statement. This materializes the entire table in memory. The SQL system property MAX_MATERIALIZED_QUERY_SIZE sets the maximum amount of bytes that a materialized query can use. The default is 64 MB.

Deterministic versus Not Deterministic

A deterministic UDF means that the function is guaranteed to return the same results given the same arguments. A UDF is not deterministic when it may return different results on successive calls with the same arguments. A query using a UDF declared with NOT DETERMINISTIC will re-evaluate the function at every row. The default is NOT DETERMINISTIC.

NuoDB does not validate whether a function is actually deterministic. This is something the user must ensure. If the function is marked as deterministic, NuoDB will treat it as such, regardless of what the function actually does.

See Cache below.

Name Resolution

The name resolution scope for functions prioritizes system functions over user-defined functions. If a user creates a function with the same name and parameter count as an existing system function, the system function will be called. To invoke the user-defined function, you must use the fully qualified name.

Language

The language for a stored procedure, user defined function or trigger may be SQL or Java. This is specified by declaring the procedure, function or trigger with LANGUAGE SQL or LANGUAGE JAVA. The default language, if none is specified, is SQL. SQL stored procedures, UDFs, and triggers are defined by sql_code_block. See SQL Procedural Language for more information.

Stored procedures, user defined functions and triggers written in Java must use the EXTERNAL keyword. See Using Embedded Java Stored Procedures.

Security

You may declare a stored procedure or user defined function to work in two different ways with respect to the calling user’s privileges on database objects affected. The procedure or function may be declared in one of two ways:

SECURITY INVOKER

Checks the privileges of the calling user. If a stored procedure or user defined function is inserting, selecting, and/or updating one or more tables, the calling user has to have the appropriate privilege for the operation on each table.

SECURITY DEFINER

Specifies that the stored procedure or user defined function is to be executed with the privileges of the user that created it. For example, the calling user may not have privileges to SELECT or INSERT INTO tables when referencing them outside the stored procedure or user defined function, but will be allowed access when executing the stored procedure or function. If a security level is not specified, this is the default.

Whoever gets the execution rights on the procedure can access the same tables/schema/sequence/etc.. of the creator of the procedure, even if the current user is still the invoker.

External Definition

Declaring a UDF as EXTERNAL is done to describe a dynamically loadable Java function that is to be executed. For LANGUAGE JAVA, the EXTERNAL function defined by sp_routine must be in the format classid:fully_qualified_class.method, where classid is the ID used in the CREATE JAVACLASS command used to upload to the NuoDB server the JAR with the defined code. See CREATE JAVACLASS.

SQL Procedural Language - CREATE FUNCTION body

If defining this UDF in SQL, sql_code_block is one or more SQL statements. See SQL Procedural Language for more information.

Cache

Deterministic user defined functions are stored in an in-memory cache. The size of this cache is limited by the system property UDF_CACHE_SIZE. For more information, see SQL System Properties. The cache stores any type of user defined function, regardless of how it is implemented (Java or SQL). A deterministic user defined function is one that will return the same result when it is run with the same arguments.

NuoDB does not validate whether a function is actually deterministic. This is something the user must ensure. If the function is marked as deterministic, NuoDB will treat it as such, regardless of what the function actually does.

When the cache is full, the oldest entry gets removed, so if that combination of arguments is used again, the user defined function code will be executed to compute the result. The default is 50. An example is as follows:

  • A table contains the values (1), (2), (3), (1), (1), (4), (1), (2), (1), (20), (3), (1)

  • Execute “SELECT process(value) FROM data” where process is a user defined function.

  • With a default UDF_CACHE_SIZE of 50, process() is executed only five times, for these argument values: 1, 2, 3, 4, 20.

  • With a UDF_CACHE_SIZE of two, process() gets invoked nine times, for these values: 1, 2, 3, 1, 4, 2, 20, 3, 1.

Call to process() Value Cache

1

1

1

2

2

1,2

3

3

2,3

4

1

3,1

-

1

3,1

5

4

1,4

-

1

4,1

6

2

1,2

-

1

2,1

7

20

1,20

8

3

20,3

9

1

3,1

  • With a UDF_CACHE_SIZE of 0, process() is invoked all the time: for 1, 2, 3, 1, 1, 4, 1, 2, 1, 20, 3, 1.

System Tables

The body of the function is stored in SYSTEM.FUNCTIONS in the FUNCTIONTEXT column. See FUNCTIONS System Table Description and Example 1 below.

Parameters

schema

Name of the schema in which the function will be created. The default is the current schema as defined by the USE command. If schema is provided, and the schema does not exist, it will be created. Functions cannot be created in the SYSTEM schema. This schema is reserved for database metadata.

name

Optionally schema-qualified name of the function.

arg_name

Variable name of the input parameter(s).

arg_type

SQL data type of the input parameter(s).

return_data_type

SQL data type of the value being returned from the function.

table_name

Name of the table being returned from the function.

column_name

Describes one or more columns being returned from the function. When using a function in a FROM clause, these are the column names in the result set.

column_type

SQL data type for each column_name in the table being returned from the function. This data type must be compatible with the data type of the source data. For example, a VARCHAR data type can be selected or inserted into a STRING data type, but not into an INTEGER data type.

When using the nuosql command line utility to create or alter multi-statement functions, the SET DELIMITER command must be used prior to defining the function. This new delimiter is used to indicate the end of the function definition and will execute the CREATE FUNCTION statement. See the examples below.

Examples

Example 1: Using a function as an item in a SELECT list.

If the function declares one or more arguments, they will appear as variables to all the statements of the function.

DROP FUNCTION IF EXISTS func_is_date;
SET DELIMITER @
CREATE FUNCTION func_is_date (i_date string)
       RETURNS BOOLEAN
       DETERMINISTIC
       LANGUAGE SQL
       SECURITY INVOKER
AS
   VAR l_out BOOLEAN = 'TRUE';
   VAR l_timestamp TIMESTAMP;
   try
       l_timestamp = (SELECT cast(DATE(i_date) as timestamp) FROM DUAL);
   catch(error)
       l_out = 'FALSE';
   end_try;
   RETURN l_out;
END_FUNCTION;
@
SET DELIMITER ;

SELECT func_is_date('2014-08-01') from dual;
 FUNC_IS_DATE
 ------------
     TRUE

SELECT func_is_date('2014-09-45') from dual;
 FUNC_IS_DATE
 -------------
     FALSE
SET OUTPUT VERTICAL;
SELECT * FROM system.functions;
==================================== Row #1 ====================================
SCHEMA: TEST
FUNCTIONNAME: FUNC_IS_DATE
PARAMETERCOUNT: 1
CREATOR: DBA
FUNCTIONTYPE: 0
FUNCTIONTEXT: (i_date string)
       RETURNS BOOLEAN
       DETERMINISTIC
       LANGUAGE SQL
       SECURITY INVOKER
AS
   VAR l_out BOOLEAN = 'TRUE';
   VAR l_timestamp TIMESTAMP;
   try
       l_timestamp = (SELECT cast(DATE(i_date) as timestamp) FROM DUAL);
   catch(error)
       l_out = 'FALSE';
   end_try;
   RETURN l_out;
END_FUNCTION
VERSION: 1
Example 2: Overloading a function name.
SET DELIMITER @
CREATE FUNCTION function1 (in_parm1 STRING)
      RETURNS STRING
AS
      THROW 'function1 with 1 parameter';
END_FUNCTION;
@

CREATE FUNCTION function1 (in_parm1 STRING, in_parm2 STRING)
      RETURNS STRING
AS
      THROW 'function1 with 2 parameters';
END_FUNCTION;
@
SET DELIMITER ;

SELECT function1('string1') FROM DUAL;
function1 with 1 parameter

SELECT function1('string1','string2') FROM DUAL;
function1 with 2 parameters

DROP FUNCTION function1;
/* function TEST.FUNCTION1 is an overloaded function: use the syntax */
/* <schema>.<name>/<parameter count> to identify the one to be dropped */

DROP FUNCTION function1/1;
DROP FUNCTION function1/2;
Example 3: Using a function as an item in a SELECT list and in a WHERE clause.

Select all players that played for the Chicago Blackhawks in 2010:

SET DELIMITER @
CREATE FUNCTION fnc_position (i_position STRING)
      RETURNS string
AS
      RETURN (SELECT CASE i_position
                WHEN 'C' then 'Center'
                WHEN 'D' then 'Defense'
                WHEN 'G' then 'Goalie'
                WHEN 'L' then 'Left Wing'
                WHEN 'R' then 'Right Wing'
                WHEN 'F' then 'Forward'
                ELSE 'UnKnown'
               END FROM DUAL);
END_FUNCTION;
@
CREATE FUNCTION fnc_get_team (i_playerid STRING, i_year STRING)
      RETURNS string
AS
      RETURN (SELECT t.name FROM SCORING s, TEAMS t
        WHERE s.playerid = i_playerid
          AND s.year = i_year
          AND s.stint = 1
          AND s.year = t.year
          AND s.teamid = t.teamid);
END_FUNCTION;
@
SET DELIMITER ;

SELECT FIRSTNAME, LASTNAME, fnc_position(position) as position_name
FROM PLAYERS
WHERE fnc_get_team(playerid,'2010') = 'Chicago Blackhawks'
 ORDER BY fnc_position(position), lastname, firstname;
 FIRSTNAME   LASTNAME   POSITION_NAME
 ---------- ----------- --------------
  Dave      Bolland       Center
  Evan      Brophey       Center
  Jacob     Dowell        Center
  ...
Example 4: Using a function as an item in a SELECT list when creating a view.

A function used in a view cannot be dropped without first dropping the view.

In the following example the VIEW is created first, then the rows are ordered in the output using an ORDER BY in the SQL SELECT statement. It is not good practice to use ORDER BY in a VIEW definition. To improve performance, the NuoDB optimizer will ignore ordering in a VIEW, unless a LIMIT or OFFSET is supplied in the VIEW definition, along with the ORDER BY.
DROP FUNCTION IF EXISTS fnc_position;
SET DELIMITER @
CREATE FUNCTION fnc_position (i_position STRING)
      RETURNS string
AS
      RETURN (SELECT CASE i_position
                WHEN 'C' then 'Center'
                WHEN 'D' then 'Defense'
                WHEN 'G' then 'Goalie'
                WHEN 'L' then 'Left Wing'
                WHEN 'R' then 'Right Wing'
                WHEN 'F' then 'Forward'
                ELSE 'UnKnown'
               END FROM DUAL);
END_FUNCTION;
@
SET DELIMITER ;

CREATE VIEW vw_players_list AS
 SELECT FIRSTNAME, LASTNAME, fnc_position(position) as position_name
  FROM PLAYERS;

SELECT * FROM vw_players_list ORDER BY position_name;
   FIRSTNAME     LASTNAME   POSITION_NAME
 ------------- ------------ --------------
 Antti         Aalto            Center
 Reg           Abbott           Center
 Sid           Abel             Center
  ...

DROP FUNCTION IF EXISTS fnc_position;
#Error: function "USER.FNC_POSITION/1" is referenced by view USER.VW_PLAYERS_LIST and cannot be dropped
DROP VIEW vw_players_list;
DROP FUNCTION IF EXISTS fnc_position;
Example 5: Using a table function in a FROM clause. This uses a RETURN (SELECT…​.) statement.
DROP FUNCTION IF EXISTS fnc_tbl_get_teams;
SET DELIMITER @
CREATE FUNCTION fnc_tbl_get_teams (i_PlayerId STRING)
      RETURNS TABLE fnc_tbl_get_teams (
          PlayerId     STRING,
          year         INTEGER,
          position     VARCHAR(4),
          team_name    VARCHAR(48),
          stint        INTEGER,
          gamesplayed  INTEGER,
          conferenceid VARCHAR(2),
          Divisionid   VARCHAR(2) )
AS
    RETURN ( SELECT
              CAST(s.playerId AS STRING) AS PLAYERID,
              s.year,
              s.position,
              t.name AS TEAM_NAME,
              s.stint,
              s.gamesplayed,
              t.conferenceid,
              t.divisionid FROM scoring s, teams t
            WHERE s.playerId = i_PlayerId
               AND s.year = t.year
               AND s.teamid = t.teamid );
END_FUNCTION;
@

SET DELIMITER ;

SELECT p.playerid,
    p.firstname as FIRST,
    p.lastname as LAST,
    f.year,
    f.position as POS,
    f.team_name,
    f.stint,
    f.gamesplayed as GAMES,
    f.conferenceid as CONF,
    f.divisionid as DIV
FROM players p, fnc_tbl_get_teams(p.playerid) f
 WHERE p.firstnhl = 2010 and p.lastnhl = 2011
 ORDER BY p.playerid, f.year, f.stint;
 PLAYERID  FIRST LAST  YEAR  POS     TEAM_NAME       STINT  GAMES CONF DIV
 --------- ----- ----- ----- --- ------------------- ------ ----- ---- ---
 adamlu01  Luke  Adam  2010   L  Buffalo Sabres        1      19   EC  NE
 adamlu01  Luke  Adam  2011   L  Buffalo Sabres        1      52   EC  NE
 aulieke01 Keith Aulie 2010   D  Toronto Maple Leafs   1      40   EC  NE
 aulieke01 Keith Aulie 2011   D  Toronto Maple Leafs   1      17   EC  NE
 aulieke01 Keith Aulie 2011   D  Tampa Bay Lightning   2      19   EC  SE
...
Example 6: Using a table function in a FROM clause. This uses an INSERT INTO…​SELECT…​ statement with a RETURN specification.
DROP FUNCTION IF EXISTS fnc_tbl_get_teams;
SET DELIMITER @
CREATE FUNCTION fnc_tbl_get_teams (i_PlayerId STRING)
      RETURNS TABLE fnc_tbl_get_teams (
        PlayerId     STRING,
        year         STRING,
        position     STRING,
        team_name    STRING,
        stint        STRING,
        games_played INTEGER,
        conference   STRING,
        Division     STRING )
AS
      INSERT INTO fnc_tbl_get_teams
        SELECT s.playerId,
               s.year,
               s.position,
               t.name,
               s.stint,
               s.gamesplayed,
               t.conferenceid,
               t.divisionid
          FROM scoring s, teams t
         WHERE s.playerId = i_PlayerId
           AND s.year = t.year
           AND s.teamid = t.teamid;
      RETURN;
END_FUNCTION;
@

SET DELIMITER ;

SELECT p.playerid,
    p.firstname as FIRST,
    p.lastname as LAST,
    f.year,
    f.position as POS,
    f.team_name,
    f.stint,
    f.games_played as GAMES,
    f.conference as CONF,
    f.division as DIV
FROM players p, fnc_tbl_get_teams(p.playerid) f
 WHERE p.firstnhl = 2010 and p.lastnhl = 2011
 ORDER BY p.playerid, f.year, f.stint;
 PLAYERID   FIRST LAST YEAR  POS        TEAM_NAME      STINT  GAMES CONF DIV
 --------- ------ ---- ----- --- --------------------- ------ ----- ---- ---
 adamlu01  Luke   Adam  2010  L   Buffalo Sabres          1   19     EC  NE
 adamlu01  Luke   Adam  2011  L   Buffalo Sabres          1   52     EC  NE
 aulieke01 Keith  Aulie 2010  D   Toronto Maple Leafs     1   40     EC  NE
 aulieke01 Keith  Aulie 2011  D   Toronto Maple Leafs     1   17     EC  NE
 aulieke01 Keith  Aulie 2011  D   Tampa Bay Lightning     2   19     EC  SE
...
Example 7: A function name as a table and table alias.
DROP FUNCTION IF EXISTS fnc_format_name;
SET DELIMITER @
CREATE FUNCTION fnc_format_name (team_id STRING)
  RETURNS TABLE output (fullname VARCHAR(50))
AS
  RETURN ( SELECT firstname||' '||lastname AS fullname
             FROM players
            WHERE playerid IN (SELECT playerid
                                 FROM scoring
                                WHERE teamid = team_id)
         );
END_FUNCTION;
@
SET DELIMITER ;

SELECT fnc_format_name.fullname FROM fnc_format_name('CHI') LIMIT 3;
   FULLNAME
 -------------
 Clarence Abel
 Sid Abel
 Craig Adams

For more information and examples, see SQL Procedural Language.