Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (A-D)

 

CREATE FUNCTION statement (external procedures)

Use this statement to create an interface to a native or external function. To create a user-defined SQL function, see CREATE FUNCTION statement.

Syntax
CREATE [ OR REPLACE ] FUNCTION [ owner.]function-name 
( [ parameter, ... ] )
RETURNS data-type 
[ SQL SECURITY { INVOKER | DEFINER } ]
[ [ NOT ] DETERMINISTIC ]
EXTERNAL NAME external-call [ LANGUAGE environment-name  ]
parameter :
   [ IN ] parameter-name data-type [ DEFAULT expression ]
environment-name : 
  C_ESQL32
| C_ESQL64
| C_ODBC32
| C_ODBC64
| CLR
| JAVA
| PERL
| PHP
Parameters
  • CREATE FUNCTION   You can create permanent stored functions that call external or native functions written in a variety of programming languages.

    Parameter names must conform to the rules for other database identifiers such as column names. They must be a valid SQL data type. For a list of valid data types, see SQL data types.

    Parameters can be prefixed with the keyword IN. However, function parameters are IN by default.

    • IN   The parameter is an expression that provides a value to the function.

    When functions are executed, 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 when the function is executed, and no default is set, an error is given.

    Specifying OR REPLACE (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 permissions.

    The EXTERNAL NAME clause is not supported for TEMPORARY functions.

  • [ 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, 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 must be used 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.

  • 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, care should be taken to 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).

  • EXTERNAL NAME native-call clause  

    native-call :
    [operating-system:]function-name@library; ...
    
    operating-system : Unix
    

    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.

    The library name can include 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. The following is a formal 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 above 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. The native function will execute as part of the server. In this case, if the function causes a fault, then the database server will be terminated. Because of this, loading and executing functions in an external environment using the LANGUAGE attribute is recommended. If a function causes a fault in an external environment, the database server will continue to run.

    For information about native library calls, see Calling external libraries from procedures.

  • EXTERNAL NAME c-call LANGUAGE {C_ESQL32 | C_ESQL64 | C_ODBC32 | C_ODBC64 } 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 specifying one of C_ESQL32, C_ESQL64, C_ODBC32, or C_ODBC64.

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

    The following is a sample function definition.

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

    For more information, see The ESQL and ODBC external environments.

  • EXTERNAL NAME clr-call LANGUAGE CLR clause   To call a .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 with the exception that the code for the procedure or function is written in a .NET language such as C# or Visual Basic, and the execution of the procedure or function takes place outside the database server (that is, within a separate .NET executable).

    The following is a sample function definition.

    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 )' 
    LANGUAGE CLR;

    For more information, see The CLR external environment.

  • EXTERNAL NAME perl-call LANGUAGE PERL 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 with the exception 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).

    The following is a sample function definition.

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

    For more information, see The PERL external environment.

  • EXTERNAL NAME php-call LANGUAGE PHP 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 with the exception 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).

    The following is a sample function definition.

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

    For more information, see The PHP external environment.

  • EXTERNAL NAME java-call LANGUAGE JAVA 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 with the exception 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 Virtual Machine).

    The following is a sample function definition.

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

    For more information, see The Java external environment.

Remarks

The CREATE FUNCTION statement creates a function in the database. Users with DBA authority 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. See Referencing temporary tables within procedures.

Permissions

Must have RESOURCE authority, unless creating a temporary function.

Must have DBA authority for external functions or to create a function for another user.

Side effects

Automatic commit.

See also
Standards and compatibility
  • SQL/2003   Persistent Stored Module feature. The syntax extensions for Java result sets are as specified in the optional J621 feature.