CREATE FUNCTION statement [External call]

Parameter names must conform to the rules for database identifiers. They must have a valid SQL data type, and must be prefixed by the keyword IN, signifying that the argument is an expression that provides a value to the function. However, function parameters are IN by default.

When functions are executed, then not all parameters need to be specified. If a DEFAULT value is provided in the CREATE FUNCTION statement, missing parameters are assigned the default values. If an argument is not provided by the caller and no default is set, then an error is given.

• OR REPLACE clause

  Specifying CREATE OR REPLACE FUNCTION creates a new function, or replaces an existing function with the same name. This clause changes the definition of the function, but preserves existing privileges.

• RETURNS clause

  Use the RETURNS clause to specify the data type for the result of the function. The RETURNS clause must be the first clause of the statement.

• SQL SECURITY clause

  The SQL SECURITY clause defines whether the function is executed as the INVOKER (the user who is calling the function), or as the DEFINER (the user who owns the function). The default is DEFINER. For external calls, this clause establishes the ownership context for unqualified object references in the external environment.

  When SQL SECURITY INVOKER is specified, more memory is used because annotation must be done for each user that calls the function. Also, when SQL SECURITY INVOKER is specified, name resolution is done as the invoker as well. Therefore, qualify all object names (tables, procedures, and so on) with their appropriate owner. For example, suppose user1 creates the following function:

  CREATE FUNCTION user1.myFunc()
     RETURNS INT
     SQL SECURITY INVOKER
     BEGIN
       DECLARE res INT;
       SELECT COUNT(*) INTO res FROM table1;
       RETURN res;
     END;

  If user2 attempts to run this function and a table user2.table1 does not exist, a table lookup error results. Additionally, if a user2.table1 does exist, that table is used instead of the intended user1.table1. To prevent this situation, qualify the table reference in the statement (user1.table1, instead of just table1).

• [ NOT ] DETERMINISTIC clause

  Use this clause to indicate whether functions are deterministic or non-deterministic. If this clause is omitted, then the deterministic behavior of the function is unspecified (the default).

  If a function is declared as DETERMINISTIC, then it should return the same value every time it is invoked with the same set of parameters.

  If a function is declared as NOT DETERMINISTIC, then it is not guaranteed to return the same value for the same set of parameters. A function declared as NOT DETERMINISTIC is re-evaluated each time it is called in a query. This clause is required when it is known that the function result for a given set of parameters can vary.

  Also, functions that have side effects such as modifying the underlying data should be declared as NOT DETERMINISTIC. For example, a function that generates primary key values and is used in an INSERT...SELECT statement should be declared NOT DETERMINISTIC:

  CREATE FUNCTION keygen( increment INTEGER )
  RETURNS INTEGER
  NOT DETERMINISTIC
  BEGIN
    DECLARE keyval INTEGER;
    UPDATE counter SET x = x + increment;
    SELECT counter.x INTO keyval FROM counter;
    RETURN keyval
  END
  INSERT INTO new_table
  SELECT keygen(1), ...
  FROM old_table;

  Functions can be declared as DETERMINISTIC if they always return the same value for given input parameters.

• EXTERNAL NAME native-call clause

  The EXTERNAL NAME clause is not supported for TEMPORARY functions.

  A function using the EXTERNAL NAME clause with no LANGUAGE attribute defines an interface to a native function written in a programming language such as C. The native function is loaded by the database server into its address space.

  Because native-call can specify multiple sets of operating systems, processors, libraries, and functions, more-precisely specified configurations take precedence over less-precisely defined configurations. For example, Solaris(X86_64):myfunc64@mylib.so takes precedence over Solaris:myfunc64@mylib.so.

  For syntaxes that support system-configuration, if you do not specify system-configuration, then it is assumed that the procedure runs on all system configurations. Unix represents the following Unix-based operating systems: AIX, HPUX, Linux, Mac OSX, and Solaris. Windows represents all versions of the Windows operating system.

  The specific-operating-system and processor-architecture values are those operating systems and processors supported by SQL Anywhere server.

  The library name (library-file-prefix) is followed by the file extension which is typically .dll on Windows and .so on Unix. In the absence of the extension, the software appends the platform-specific default file extension for libraries. For example:

  CREATE FUNCTION mystring( IN instr LONG VARCHAR )
  RETURNS LONG VARCHAR
  EXTERNAL NAME 'mystring@mylib.dll;Unix:mystring@mylib.so';
  

  A simpler way to write the EXTERNAL NAME clause, using platform-specific defaults, is as follows:

  CREATE FUNCTION mystring( IN instr LONG VARCHAR )
  RETURNS LONG VARCHAR
  EXTERNAL NAME 'mystring@mylib';
  

  When called, the library containing the function is loaded into the address space of the database server behavior. The native function executes as part of the database server. In this case, if the function causes a fault, then the database server shuts down. Because of this behavior, loading and executing functions in an external environment using the LANGUAGE attribute is recommended. If a function causes a fault in an external environment, then the database server continues to run.

• EXTERNAL NAME c-call clause

  To call a compiled native C function in an external environment instead of within the database server, the stored procedure or function is defined with the EXTERNAL NAME clause followed by the LANGUAGE attribute.

  When the LANGUAGE attribute is specified, then the library containing the function is loaded by an external process and the external function executes as part of that external process. In this case, if the function causes a fault, then the database server continues to run.

  CREATE FUNCTION ODBCinsert( 
    IN ProductName CHAR(30),
    IN ProductDescription CHAR(50)
  )
  RETURNS INT
  EXTERNAL NAME 'ODBCexternalInsert@extodbc.dll'
  LANGUAGE C_ODBC32;

• EXTERNAL NAME clr-call clause

  To call a Microsoft .NET function in an external environment, the function interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE CLR attribute.

  A CLR stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in a Microsoft .NET language such as Microsoft C# or Microsoft Visual Basic, and the execution of the procedure or function takes place outside the database server (that is, within a separate Microsoft .NET executable).

  CREATE FUNCTION clr_interface( 
      IN p1 INT, 
      IN p2 UNSIGNED SMALLINT, 
      IN p3 LONG VARCHAR)
  RETURNS INT 
  EXTERNAL NAME 'CLRlib.dll::CLRproc.Run( int, ushort, string ) int'
  LANGUAGE CLR;

• EXTERNAL NAME perl-call clause

  To call a Perl function in an external environment, the function interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE PERL attribute.

  A Perl stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in Perl and the execution of the procedure or function takes place outside the database server (that is, within a Perl executable instance).

  CREATE FUNCTION PerlWriteToConsole( IN str LONG VARCHAR) 
  RETURNS INT
  EXTERNAL NAME '<file=PerlConsoleExample> 
      WriteToServerConsole( $sa_perl_arg0 )'
  LANGUAGE PERL;

• EXTERNAL NAME php-call clause

  To call a PHP function in an external environment, the function interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE PHP attribute.

  A PHP stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in PHP and the execution of the procedure or function takes place outside the database server (that is, within a PHP executable instance).

  CREATE FUNCTION PHPPopulateTable()
  RETURNS INT
  EXTERNAL NAME '<file=ServerSidePHPExample> ServerSidePHPSub()'
  LANGUAGE PHP;

• EXTERNAL NAME java-call clause

  To call a Java method in an external environment, the function interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE JAVA attribute.

  A Java-interfacing stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in Java and the execution of the procedure or function takes place outside the database server (that is, within a Java VM).

  CREATE FUNCTION HelloDemo( IN name LONG VARCHAR ) 
  RETURNS INT
  EXTERNAL NAME 'Hello.main([Ljava/lang/String;)I'
  LANGUAGE JAVA;

  The descriptors for arguments and return values from Java methods have the following meanings:

  Field type	Java data type
  B	byte
  C	char
  D	double
  F	float
  I	int
  J	long
  L class-name;	An instance of the class class-name. The class name must be fully qualified, and any dot in the name must be replaced by a /. For example, java/lang/String.
  S	short
  V	void
  Z	Boolean
  [	Use one for each dimension of an array.

• EXTERNAL NAME js-call clause

  To call a JavaScript function in an external environment, the procedure interface is defined with an EXTERNAL NAME clause followed by the LANGUAGE JS attribute.

  A JavaScript stored procedure or function behaves the same as a SQL stored procedure or function except that the code is written in JavaScript and the code execution takes place outside the database server (that is, within a Node.js executable instance).

  Specify the return type of the JavaScript function at the beginning of the EXTERNAL NAME string inside angle brackets. Since JavaScript does not allow pass-by-reference for simple variables inside functions, the left bracket character ([) can proceed the S, B, I, U, or D characters to indicate that a one element array is being passed to the JavaScript stored procedure. This syntax is provided to support INOUT and OUT parameters in stored procedures.

  The following is a sample function definition.

  CREATE FUNCTION SimpleJSDemo( 
      IN thousands INT, 
      IN hundreds INT, 
      IN tens INT, 
      IN ones INT) 
    RETURNS INT
    EXTERNAL NAME '<I><file=SimpleJSExample> SimpleJSFunction(IIII)' 
    LANGUAGE JS;

  The descriptors for arguments and return values from JavaScript methods have the following meanings:

  Field type	JavaScript data type
  S	String
  B	Boolean
  I	Integer
  U	Unsigned integer
  D	Double

The CREATE FUNCTION statement creates a function in the database. You can create functions for other users by specifying an owner. A function is invoked as part of a SQL expression.

When referencing a temporary table from multiple functions, a potential issue can arise if the temporary table definitions are inconsistent and statements referencing the table are cached.

If you specify an EXTERNAL NAME clause when running on Mac OS X 10.11, then you must either specify the full path to the .dylib you need to load, or place the .dylib file in the lib64 directory of the SQL Anywhere install.

You must have the CREATE PROCEDURE and CREATE EXTERNAL REFERENCE system privileges to create external functions owned by you.

You must have the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privileges, as well as the CREATE EXTERNAL REFERENCE system privilege to create external functions owned by others.

To replace an existing function, you must own the procedure or have one of the following:

• CREATE ANY PROCEDURE and DROP ANY PROCEDURE system privileges.

• CREATE ANY OBJECT and DROP ANY OBJECT system privileges.

• ALTER ANY OBJECT or ALTER ANY PROCEDURE system privileges.

Automatic commit.

• ANSI/ISO SQL Standard

  CREATE FUNCTION for an external language environment is a core feature of the ANSI/ISO SQL Standard, though some of its components supported in the software are optional ANSI/ISO SQL Language Features. A subset of these features include:

  • The SQL SECURITY clause is optional ANSI/ISO SQL Language Feature T324.

  • The ability to pass a LONG VARCHAR, LONG NVARCHAR, or LONG BINARY value to a SQL function is ANSI/ISO SQL Language Feature T041.

  • Support for LANGUAGE JAVA is optional ANSI/ISO SQL Language Feature J621.

  • The ability to create or modify a schema object within an external function, using statements such as CREATE TABLE or DROP TRIGGER, is Language Feature T653.

  • The ability to use a dynamic-SQL statement within an external function, including statements such as CONNECT, EXECUTE IMMEDIATE, PREPARE, and DESCRIBE, is Language Feature T654.

  Several clauses of the CREATE FUNCTION statement are not in the standard. These include:

  • Support for C_ESQL32, C_ESQL64, C_ODBC32, C_ODBC64, CLR, PERL, and PHP in the LANGUAGES clause are not in the standard.

  • The format of external-call is implementation-defined.

  • The optional DEFAULT clause for a specific routine parameter is not in the standard.

  • The optional OR REPLACE clause is not in the standard.

• Transact-SQL

  CREATE FUNCTION for an external routine is supported by Adaptive Server Enterprise. Adaptive Server Enterprise only supports LANGUAGE JAVA as the external environment (SQL Language Feature J621) for an external function.