SQL Language Guide > SQL Language Guide > Elements of SQL Statements > Scalar User-defined Functions (UDFs)
Was this helpful?
Scalar User-defined Functions (UDFs)
Custom scalar functions can be created for use in queries to extend database functionality. Use the CREATE FUNCTION statement to define a function and the DROP FUNCTION statement to remove it. Functions can be written in SQL, JavaScript, and Python. UDFs are supported for X100 tables only.
Note:  SQL and JavaScript UDFs are a development release. Please work with Actian Support before using such UDFs in a production environment.

Python UDFs are in beta status and disabled by default. Do not use Python UDFs in production until Actian certifies this functionality as production ready. Please contact Actian Support if you are interested in beta testing.
How to Set up User-defined Functions
The process for configuring the use of UDFs is as follows:
1. Specify the languages (other than SQL) that UDFs are implemented in on the installation configuration parameter ii.hostname.config.udf_languages in config.dat.
Valid values for udf_languages are:
none - No languages are available; UDFs cannot be created.
javascript - (Default) Only JavaScript UDFs are available.
python - Only Python UDFs are available.
javascript,python - Both JavaScript and Python UDFs are available.
For example:
iisetres ii.hostname.config.udf_languages javascript,python
Notes:
If udf_languages is changed after UDFs are created, existing UDFs using the language will report an error on X100 startup and when used in queries.
If language support cannot be loaded for a specified language, then the database will not start and the createdb command will fail.
2. Perform the following additional steps if using Python UDFs:
Linux:
a. Install Python 3.6.8 libraries using the Python 3.6.8 installer appropriate for your operating system and version.
Note:  Do not change the default version of Python on your operating system. Install 3.6.8 in a separate area and tell Vector where to find the library.
b. Create a symbolic link in the /usr/lib directory of your installation, pointing libpython3.6m.so.rh-python36-1.0 to the Python 3.6.8 library to be used. For example:
ln -s /usr/lib64/libpython3.6m.so.1.0 /vector/opt/Actian/VectorVW/ingres/lib
/libpython3.6m.so.rh-python36-1.0
Note:  The Linux environment variable PYTHONHOME must either be empty or set to a valid path.
Windows:
a. Install Python 3.6.8 libraries using the Python 3.6.8 installer appropriate for your operating system and version.
b. Set the Vector environment variable UDF_PYTHONHOME to the path where Python is installed. For example:
ingsetenv UDF_PYTHONHOME C:\Python36
c. Set the Windows System environment variable PYTHONHOME in System Properties (select Control Panel, System, Advanced system settings, Environment variables, System variables) to the path where Python is installed. For example:
PYTHONHOME=C:\Python36
Also see CREATE FUNCTION.
Last modified date: 11/09/2022