Using HAWQ Built-In Languages

This section provides an introduction to using the HAWQ built-in languages.

HAWQ supports user-defined functions created with the SQL and C built-in languages. HAWQ also supports user-defined aliases for internal functions.

Enabling Built-in Language Support

Support for SQL and C language user-defined functions and aliasing of internal functions is enabled by default for all HAWQ databases.

Defining SQL Functions

SQL functions execute an arbitrary list of SQL statements. The SQL statements in the body of a SQL function must be separated by semicolons. The final statement in a non-void-returning SQL function must be a SELECT that returns data of the type specified by the function’s return type. The function will return a single or set of rows corresponding to this last SQL query.

The following example creates and calls a SQL function to count the number of rows of the table named orders:

Aliasing Internal Functions

Many HAWQ internal functions are written in C. These functions are declared during initialization of the database cluster and statically linked to the HAWQ server. See Built-in Functions and Operators for detailed information about HAWQ internal functions.

You cannot define new internal functions, but you can create aliases for existing internal functions.

The following example creates a new function named all_caps that is an alias for the upper HAWQ internal function:

For more information about aliasing internal functions, refer to Internal Functions in the PostgreSQL documentation.

Defining C Functions

You must compile user-defined functions written in C into shared libraries so that the HAWQ server can load them on demand. This dynamic loading distinguishes C language functions from internal functions that are written in C.

The CREATE FUNCTION call for a user-defined C function must include both the name of the shared library and the name of the function.

If an absolute path to the shared library is not provided, an attempt is made to locate the library relative to the: