Netezza Performance Server SQL command help

There are several commands that you can use to display information about functions and aggregates.
  • For functions, you can use the SHOW FUNCTION SQL command or the \df nzsql command argument.
  • For aggregates, you can use the SHOW AGGREGATE SQL command or the \da nzsql command argument.
  • For shared libraries, you can use the SHOW LIBRARY SQL command or the \dl nzsql command argument.

The SQL commands and the nzsql switches provide the same output information; they show information for all functions or aggregates such as the standard Netezza® SQL built-in functions and aggregates as well the UDFs and UDAs in the database, or in the schema for systems that support multiple schemas. The admin user can see all UDXs in the database, the database owner can see all objects in the database, and the schema owner can see all objects in the schema. Otherwise, users can see information only about the built-in objects, the objects they own, or those to which they are granted access.

The following is a sample \df command and its output. The output is truncated for easier viewing. (The output is identical for the SHOW FUNCTION command.)
MYDB.SCHEMA(MYUSER)=> \df 
                       List of functions
SCHEMA            | RESULT  | FUNCTION     | BUILTIN | ARGUMENTS
------------------+---------+--------------+---------+----------
ADMIN             |SMALLINT | YEAR         | f       | (DATE)
DEFINITION_SCHEMA |BIGINT   | ABS          | t       | (BIGINT)
DEFINITION_SCHEMA |INTEGER  | ABS          | t       | (INTEGER)
ADMIN             |INTEGER  | CUSTOMERNAME | f       | (CHARACTER VARYING (64000))

In the sample output, the UDF customername shows ‘f’ (false) for the BUILTIN value. Standard functions and aggregates that are supplied with Netezza Performance Server SQL by default are called built-ins, and they show a value of ‘t’ (true). The SCHEMA information shows the database schema where the function is defined. Built-in functions like abs() are in the DEFINITION_SCHEMA, while user-defined functions are in the database default schema or in another schema if the system supports multiple schemas.

Use the plus sign switch (\df+, \da+, and \dl+) or the VERBOSE option for the SQL commands to obtain verbose output, which includes the comments that are specified for them if you follow the process in the previous section.