Declaring a Scalar UDF

To declare an in-process external UDF owned by themselves, a user requires both the CREATE PROCEDURE and CREATE EXTERNAL REFERENCE system privileges. To declare an in-process external UDF which is owned by another user requires either the CREATE ANY PROCEDURE or CREATE ANY OBJECT system privilege, as well as the CREATE EXTERNAL REFERENCE system privilege.

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, 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.