Creating and executing user-defined functions

User-defined functions (UDFs) are a class of procedures that return a single value to the calling environment. This section introduces creating, using, and dropping user-defined functions.

There are four steps required to create and use a UDF:

1. Declare the user-defined function using the CREATE AGGREGATE FUNCTION statement or the CREATE FUNCTION statement.
2. Implement the function entry point(s) in C/C++.
3. Compile the implementation and link it as a shared library.
4. Use the function within a SQL statement anywhere that you would use a built-in SQL function.

• Creating user-defined functions
  Use the CREATE FUNCTION or CREATE AGGREGATE FUNCTION statements to create user-defined functions. You must have Resource authority to execute this statement.
• User-defined function restrictions
  External C/C++ user-defined functions must have the following restrictions.
• Calling user-defined functions
  You can use a user-defined function, subject to permissions, in any place you use a built-in nonaggregate function.
• Dropping user-defined functions
  Once you create a user-defined function, it remains in the database until it is explicitly removed. Only the owner of the function or a user with DBA authority can drop a function from the database.
• Granting and revoking permissions
  A user-defined function is owned by the user who created it, and that user can execute it without permission. The owner of a user-defined function can grant permissions to other users with the GRANT EXECUTE command.
• Compile and link switches for building dynamically linkable libraries
  Use the following compile and link switches when building dynamically linkable libraries for any user-defined function.
• SQL data types
  UDF declarations support only certain SQL data types.

---

Created November 2, 2009. Send feedback on this help topic to Sybase Technical Publications: pubs@sybase.com