CREATE FUNCTION (SQL)

Creates a function as a method in a class.

Synopsis

CREATE FUNCTION name(parameter_list) [characteristics]     [ LANGUAGE SQL ]    BEGIN code_body ;    END   CREATE FUNCTION name(parameter_list) [characteristics]     LANGUAGE OBJECTSCRIPT    { code_body } 
 CREATE FUNCTION name(parameter_list) [characteristics]      LANGUAGE { JAVA | PYTHON
| DOTNET } EXTERNAL NAME external-stored-procedure

Description

The CREATE FUNCTION statement creates a function as a method in a class. This class method is projected as an SQL Stored Procedure. You can also use the CREATE PROCEDURE statement to create a method which is projected as an SQL Stored Procedure. CREATE FUNCTION should be used when the method is to return a value, but it can be used to create a method that does not return a value.

The optional keyword OR REPLACE allows you to modify or replace an existing function. CREATE OR REPLACE FUNCTION has the same effect as invoking DROP FUNCTION to delete the old version of the function and then invoking CREATE TRIGGER.

In order to create a function, you must have %CREATE_FUNCTION administrative privilege, as specified by the GRANT command.

You cannot create a function in a class if the class definition is a deployed class. This operation fails with an SQLCODE -400 error alongside the %msg Unable to execute DDL that modifies a deployed class: 'classname'.

For information on calling SQL functions from within SQL statements, refer to User-defined Functions in the “Querying the Database” chapter of Using InterSystems SQL. For calling SQL stored procedures in a variety of contexts, refer to the CALL statement.

Arguments

name

The name of the function to be created in a stored procedure class. The name must be a valid identifier and must be followed by parentheses, even if no parameters are specified. This name may be unqualified (StoreName) and take the default schema name, or qualified by specifying the schema name (Patient.StoreName). You can use the $SYSTEM.SQL.Schema.Default()Opens in a new tab method to determine the current system-wide default schema name. The initial system-wide default schema name is SQLUser, which corresponds to the class package name User.

Note that the FOR characteristic (described below) overrides the class name specified in name. If a function with this name already exists, the operation fails with an SQLCODE -361 error.

The name of the generated class is the package name corresponding to the schema name, followed by a dot, “func”, and then the specified name. For example, if the unqualified function name RandomLetter takes the initial default schema SQLUser, the resulting class name would be: User.funcRandomLetter. For further details, see SQL to Class Name Transformations in the “Defining and Using Stored Procedures” chapter of Using InterSystems SQL.

InterSystems SQL does not allow you to specify a duplicate function name that differs only in letter case. Specifying a function name that differs only in letter case from an existing function name results in an SQLCODE -400 error.

parameter-list

An optional list of parameters used to pass values to the function. The parameter list is enclosed in parentheses, which are mandatory even when no parameters are specified, and parameter declarations in the list are separated by commas. Each parameter declaration in the list consists of (in order):

An optional keyword specifying whether the parameter mode is IN (input value), OUT (output value), or INOUT (modify value). If omitted, the default parameter mode is IN.
The parameter name. Parameter names are case-sensitive.
The data type of the parameter.
Optional: A default value for the parameter. You can specify the DEFAULT keyword followed by a default value; the DEFAULT keyword is optional. If no default is specified, the assumed default is NULL.

The following example specifies two input parameters, both of which have default values. The optional DEFAULT keyword is specified for the first parameter, omitted for the second parameter:

CREATE FUNCTION RandomLetter(IN firstlet CHAR DEFAULT 'A',IN lastlet CHAR 'Z')
BEGIN
-- SQL program code
END

User-defined functions are supplied to the clauses of a user-defined aggregate function. When defining a function for use in a user-defined aggregate function, you define a state parameter which is used to aggregate and pass the output value.

A function is “correlated” if it takes at least one parameter that is dependent on a value from a row of data, for example the %ID field. Correlated functions are evaluated per row; uncorrelated functions (that is, functions that either take no parameters or take arguments that remain consistent across all rows) are evaluated a single time.

characteristics

An optional argument that consists of one or more keywords specifying the characteristics of the function. Multiple characteristics are separated by whitespace (a space or line break), and characteristics can be specified in any order. The available keywords are as follows:

FOR className	Specifies the name of the class in which to create the function. If the class does not exist, it will be created. You can also specify a class name by qualifying the function name. The class name specified in the FOR clause overrides a class name specified by qualifying the function name.
FINAL	Specifies that subclasses cannot override the function. By default, functions are not final. The FINAL keyword is inherited by subclasses.
PRIVATE	Specifies that the function can only be invoked by other function of its own class or subclasses. By default, a function is public, and can be invoked without restriction. This restriction is inherited by subclasses.
PROCEDURE	Specifies that the function is projected as an SQL stored procedure. Stored procedures are inherited by subclasses. Because CREATE FUNCTION always projects an SQL stored procedure, this keyword is optional. This keyword can be abbreviated as PROC.
RETURNS datatype	Specifies the data type of the value returned by a call to the function. If RETURNS is omitted, the function cannot return a value. This specification is inherited by subclasses, and can be modified by subclasses. This datatype can specify type parameters such as MINVAL, MAXVAL, and SCALE. For example RETURNS DECIMAL(19,4). Note that when returning a value, InterSystems IRIS ignores the length of datatype; for example, RETURNS VARCHAR(32) can receive a string of any length that is returned by a call to the function.
SELECTMODE mode	Only used when LANGUAGE is SQL (the default). When specified, InterSystems IRIS adds an #SQLCOMPILE SELECT=mode statement to the corresponding class method, thus generating the SQL statements defined in the method with the specified SELECTMODE. The possible mode values are LOGICAL, ODBC, RUNTIME, and DISPLAY. The default is LOGICAL.

The SELECTMODE clause is used for SELECT query operations and for INSERT and UPDATE operations. It specifies the compile-time select mode. The value that you specify for SELECTMODE is added at the beginning of the ObjectScript class method code as: #sqlcompile select=mode. For further details, see #sqlcompile select in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using ObjectScript.

In a SELECT query, the SELECTMODE specifies the mode in which data is returned. If the mode value is LOGICAL, then logical (internal storage) values are returned. For example, dates are returned in $HOROLOG format. If the mode value is ODBC, logical-to-ODBC conversion is applied, and ODBC format values are returned. If the mode value is DISPLAY, logical-to-display conversion is applied, and display format values are returned. If the mode value is RUNTIME, the display mode can be set (to LOGICAL, ODBC, or DISPLAY) at execution time.
In an INSERT or UPDATE operation, the SELECTMODE RUNTIME option supports automatic conversion of input data values from a display format (DISPLAY or ODBC) to logical storage format. This compiled display-to-logical data conversion code is applied only if the select mode setting when the SQL code is executed is LOGICAL (which is the default for all InterSystems SQL execution interfaces).

When the SQL code is executed, the %SQL.StatementOpens in a new tab class %SelectModeOpens in a new tab property specifies the execution-time select mode, as described in “Using Dynamic SQL” chapter of Using InterSystems SQL. For further details on SelectMode options, refer to “Data Display Options” in the “InterSystems IRIS SQL Basics” chapter of Using InterSystems SQL.

LANGUAGE

An optional keyword clause specifying the procedure code language. Available options are:

LANGUAGE OBJECTSCRIPT (for ObjectScript) or LANGUAGE SQL. The procedure code is specified in the code_body.
LANGUAGE JAVA, LANGUAGE PYTHON, or LANGUAGE DOTNET for an SQL procedure that invokes an external stored procedure in one of these languages. The syntax for an external stored procedure is as follows:
LANGUAGE langname EXTERNAL NAME external-routine-name
Where langname is JAVA, PYTHON, or DOTNET and external-routine-name is a quoted string containing the name of an external routine in the specified language. The SQL procedure invokes an existing routine; you cannot write code in these languages within the CREATE FUNCTION statement. Stored procedure libraries in these languages are stored external to IRIS, and therefore do not have to be packaged, imported, or compiled within IRIS. The following is an example of a CREATE FUNCTION that invokes an existing JAVA external stored procedure that returns a value:
```
CREATE FUNCTION getPrice (item_name VARCHAR)
RETURNS INTEGER
LANGUAGE JAVA
EXTERNAL NAME 'Orders.getPrice'
```

If the LANGUAGE clause is omitted, SQL is the default.

code_body

The program code for the method to be created. You specify this code in either SQL or ObjectScript. SQL program code is prefaced with a BEGIN keyword and concludes with an END keyword. Each complete SQL statement within code_body end with a semicolon (;). ObjectScript program code is enclosed in curly braces, and code lines must be indented. The language used must match the LANGUAGE clause. However, code specified in ObjectScript can contain embedded SQL.

InterSystems IRIS uses the code you supply to generate the actual code of the method. If the code you specify is SQL, InterSystems IRIS provides additional lines of code when generating the method that embed the SQL in an ObjectScript “wrapper,” provide a procedure context handler (if necessary), and handle return values. The following is an example of this InterSystems IRIS-generated wrapper code:

   NEW SQLCODE,%ROWID,%ROWCOUNT,title
   &sql( SELECT col FROM tbl )
   QUIT $GET(title)

If the code you specify is OBJECTSCRIPT, the ObjectScript code must be enclosed in curly braces. All code lines must be indented from column 1, except for labels and macro preprocessor directives. A label or macro directive must be prefaced by a colon (:) in column 1.

For ObjectScript code, you must explicitly define the “wrapper” (which NEWs variables, and uses QUIT to exit and (optionally) to return a value upon completion).

When a stored procedure is called, an object of the class %Library.SQLProcContextOpens in a new tab is instantiated in the %sqlcontext variable. This procedure context handler is used to pass the procedure context back and forth between the procedure and its caller (for example, the ODBC server).

%sqlcontext consists of several properties, including an Error object, the SQLCODE error status, the SQL row count, and an error message. The following example shows the values used to set several of these:

  SET %sqlcontext.%SQLCODE=SQLCODE
  SET %sqlcontext.%ROWCOUNT=%ROWCOUNT
  SET %sqlcontext.%Message=%msg

The values of SQLCODE and %ROWCOUNT are automatically set by the execution of an SQL statement. The %sqlcontext object is reset before each execution.

Alternatively, an error context can be established by instantiating a %SYSTEM.Error object and setting it as %sqlcontext.Error.

An SQLCODE -361 error is generated if the specified function already exists. To avoid this error, use the optional OR REPLACE keyword, or drop the old function first with DROP FUNCTION.

Executing a User-defined Function

You can execute a function in a SELECT statement, such as the following:

SELECT StudentName,StudentAge,SQLUser.HalfAge() AS HalfTheAge
FROM SQLUser.MyStudents

An SQLCODE -359 error is generated if the function does not exist.

An SQLCODE -149 error is generated if the execution of the function results in a error. The type of error is described in %msg.

Examples

The following example creates the RandomLetter() function (method) stored as a procedure that generates a random capital letter. You can then invoke this function in a SELECT statement. A DROP FUNCTION is provided to delete the RandomLetter() function. Note that this example is of an uncorrelated function, so the result set of the SELECT statement will contain Names that all start with the same, randomly chosen letter and will contain the number of names that start with the randomly chosen letter. An example result set is provided.

CREATE FUNCTION RandomLetter()
RETURNS INTEGER
PROCEDURE
LANGUAGE OBJECTSCRIPT
{
:Top
 SET x=$RANDOM(90)
 IF x<65 {GOTO Top}
 ELSE {QUIT $CHAR(x)}
}

SELECT Name FROM Sample.Person
WHERE Name %STARTSWITH RandomLetter()

Abbott, Amelia P.
Adams,John J.
Alton,Lionel N.
Amblin,Stephen O.
Amory,Jennifer E.
Andrews,Olivia G.
Arias,Rowan K.
Avery,Marvin N.

DROP FUNCTION RandomLetter

The following example creates the RandomLetter() function (method) stored as a procedure that generates a random capital letter as a correlated function that depends on the changing value of %ID, though the argument itself is not used within the body of RandomLetter(). The result set of the SELECT statement will contain Names that start with different, randomly chosen letters and its length will contain a variable number of elements. An example result set is provided.

CREATE FUNCTION RandomLetter(IN id INTEGER)
RETURNS INTEGER
PROCEDURE
LANGUAGE OBJECTSCRIPT
{
:Top
 SET x=$RANDOM(90)
 IF x<65 {GOTO Top}
 ELSE {QUIT $CHAR(x)}
}

SELECT Name FROM Sample.Person
WHERE Name %STARTSWITH RandomLetter(%ID)

Alton,Lionel N.
Cooper,Peter H.
Hertz,Lana C.
Jones,Alyssa D.

The following example creates a function that invokes ObjectScript code, which in turn contains embedded SQL:

  &sql(CREATE FUNCTION TraineeName(
   SSN VARCHAR(11), 
   OUT Name VARCHAR(50) )
   PROCEDURE
    RETURNS VARCHAR(30)
    FOR SQLUser.MyStudents
    LANGUAGE OBJECTSCRIPT
    {
        NEW SQLCODE,%ROWCOUNT
        SET Name=""
        &sql(SELECT Name INTO :Name FROM Sample.Employee 
             WHERE SSN = :SSN)
        IF $GET(%sqlcontext)'= "" {
           SET %sqlcontext.%SQLCODE=SQLCODE
           SET %sqlcontext.%ROWCOUNT=%ROWCOUNT }
           QUIT Name
     })
    IF SQLCODE=0 { WRITE !,"Created a function" QUIT}
    ELSE { WRITE !,"CREATE FUNCTION error: ",SQLCODE," ",%msg,!
      &sql(DROP FUNCTION TraineeName FROM SQLUser.MyStudents) }
      IF SQLCODE=0 { WRITE !,"Dropped a function" QUIT}
      ELSE { WRITE !,"Drop error: ",SQLCODE }

It uses the %sqlcontext object, and sets its %SQLCODE and %ROWCOUNT properties using the corresponding SQL variables. Note the curly braces enclosing the ObjectScript code following the function’s LANGUAGE OBJECTSCRIPT keyword. Within the ObjectScript code there is Embedded SQL code, marked by &sql and enclosed in parentheses.

Security and Privileges

The CREATE FUNCTION command is a privileged operation that requires the user to have %Development:USE permission. Such permissions can be granted through the Management Portal. Executing a CREATE FUNCTION command without these privileges will result in an SQLCODE -99 error and the command will fail.

Users without proper permissions can still execute this command under one of two conditions:

The command is executed via Embedded SQL, which does not perform privilege checks.
The user explicitly specifies no privilege checking by, for example, calling either %Prepare() with the checkPriv argument set to 0 or %ExecDirectNoPriv() on a %SQL.StatementOpens in a new tab.