Only a DBA, or someone with DBA authority can declare an in-process external UDF. There is also a server startup option that allows an administrator to enable or disable this style of user-defined function.
After the UDF code has been written and compiled, create a SQL function that invokes the UDF from the appropriate library file, sending the input data to the UDF.
By default, all user-defined functions use the access permissions of the owner of the UDF.
The syntax for creating a scalar UDF is:
scalar-udf-declaration:
CREATE FUNCTION [ owner.]function-name
( [ parameter , ... ] )
RETURNS data-type
[ routine-characteristics ... ]
EXTERNAL NAME library-and-entry-point-name-string
parameter:
param-name data-type [ DEFAULT value ]
routine-characteristics:
[NOT] DETERMINISTIC
| { IGNORE | RESPECT } NULL VALUES
| SQL SECURITY { INVOKER | DEFINER }
The defaults for the characteristics in the above syntax are:
DETERMINISTIC RESPECT NULL VALUES SQL SECURITY DEFINER
To minimize potential security concerns, Sybase recommends that you use a fully qualified path name to a secure directory for the library name portion of the EXTERNAL NAME clause.
SQL Security
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.
SQL SECURITY INVOKERuses additional memory, because each user that calls the procedure requires annotation. Additionally, name resolution is performed on both the user name and the INVOKER. Qualify all object names (tables, procedures, and so on) with their appropriate owner.
External Name
A function using the EXTERNAL NAME clause is a wrapper around a call to a function in an external library. A function using EXTERNAL NAME can have no other clauses following the RETURNS clause. The library name may 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.
You can start the server with a library load path that includes the location of the UDF library. On UNIX variants, modify the LD_LIBRARY_PATH in the start_iq startup script. While LD_LIBRARY_PATH is universal to all UNIX variants, SHLIB_PATH is preferred on HP, and LIB_PATH is preferred on AIX.
On UNIX platforms, the external name specification can contain a fully qualified name, in which case the LD_LIBRARY_PATH is not used. On the Windows platform, a fully qualified name cannot be used and the library search path is defined by the PATH environment variable.