ALTER FUNCTION

ALTER FUNCTION — change a user defined function (UDF)

Syntax

ALTER 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

ALTER FUNCTION uses the exact same syntax as CREATE FUNCTION (see CREATE FUNCTION) and will succeed only if the function already exists. To change a function, you need to supply the body and the parameters of the function. You cannot change the number of parameters defined for a function using the ALTER FUNCTION since this would actually be creating a new function.

ALTER FUNCTION does not modify the privileges, nor the owner, of a function. If User A, for instance, creates the function and then grants to User B the right to modify it, the function will still have User A as the creator (important if SECURITY DEFINER is specified) and all the rights that User A granted User B.

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.

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.

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 - `ALTER FUNCTION` body

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

System Tables

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

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.
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. This can be used as an item in a SELECT statement and/or this can be used in a WHERE clause.
expression: Specify an expression representing the information you want to return. This can be in the form of a function or mathematical operator (see SQL Functions and Operations) or an expression. A function can also return a function. Recursive calls to the same function are supported.
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.

Example

Use ALTER FUNCTION to change the RETURNS data type. This example also shows how the number of input parameters cannot be changed, only the data type of those input parameters.

SET DELIMITER @
CREATE FUNCTION fnc_test_function (i_number integer)
  RETURNS INTEGER
AS
  RETURN i_number + 1;
END_FUNCTION;
@

SET DELIMITER ;

select fnc_test_function(1) from dual;
 [TEST.FNC_TEST_FUNCTION]
 -------------------------
             2

SET DELIMITER @
ALTER FUNCTION fnc_test_function (i_number integer, i_increment integer)
  RETURNS INTEGER
AS
  RETURN i_number + i_increment;
END_FUNCTION;
@
DDL_ERROR: can't find function "TEST"."FNC_TEST_FUNCTION"
ALTER FUNCTION fnc_test_function(i_number integer)
  RETURNS STRING
AS
  RETURN 'value plus one equals '|| cast(i_number + 1 as string);
END_FUNCTION;
@
SET DELIMITER ;

select fnc_test_function(2) from dual;
 [TEST.FNC_TEST_FUNCTION]
 -------------------------
  value plus one equals 3

SET DELIMITER @
ALTER FUNCTION fnc_test_function(i_number string)
  RETURNS integer
AS
  RETURN cast(i_number as integer) + 1;
END_FUNCTION;
@
SET DELIMITER ;

select fnc_test_function(2) from dual;
 [TEST.FNC_TEST_FUNCTION]
 -------------------------
             3