SHOW AGGREGATE

Use the SHOW AGGREGATE command to display information about one or more aggregates (built-in and UDAs). The command checks your user account privileges to ensure that you are permitted to see information about the UDAs defined in the database and schema.

Synopsis

The SHOW AGGREGATE command has the following syntax:

SHOW AGGREGATE [ALL | ident] [VERBOSE]

Inputs

The SHOW AGGREGATE command takes the following inputs:

Table 1. SHOW AGGREGATE inputs
Input	Description
ALL	Show information about all the aggregates that are defined in the database. This is the default.
`ident`	Show information about a specific aggregate that is defined in the database that begins with `ident`. You can specify a partial name, but the command returns an error if you specify a full signature.
VERBOSE	Display detailed information about the aggregates.

Output

The SHOW AGGREGATE command has the following output

Table 2. SHOW AGGREGATE output
Output	Description
`error found "(" (at char num) syntax error, unexpected '(', expecting $end`	The message that the system returns if you specify a full signature, for example: `SHOW AGGREGATE penmax(integer);`

Description

The SHOW AGGREGATE command is identical in behavior to the nzsql \da and \da+ commands.

Privileges required

Any user can run the SHOW AGGREGATE command; however, you must be the admin user, own the UDA, or have object privileges on UDAs (such as Execute, List, Alter, or Drop) to see information about UDAs in the output.

Common tasks

Use the SHOW AGGREGATE command to display information about the aggregates in a database.

Usage

The following provides sample usage.

To show the sample UDA named PenMax, use the following command:

DEV.SCHEMA(MYUSER)=> show aggregate penmax;
  NAME  | BUILTIN | ARGUMENTS | RETURNTYPE | DESCRIPTION
--------+---------+-----------+------------+-------------
 PENMAX | f       | (INTEGER) | INT4       |
(1 row)

To show verbose information for the PenMax UDA, use the following command:

DEV.SCHEMA(MYUSER)=> SHOW AGGREGATE penmax VERBOSE;
  NAME  | BUILTIN | ARGUMENTS | RETURNTYPE | VARARGS | FENCED | VERSION | DESCRIPTION |
     STATE       | LOGMASK | MEMORY | AGGTYPE | DEPENDENCIES | ENV
--------+---------+-----------+------------+---------+--------+---------+-------------+
-----------------+---------+--------+---------+--------------+-----
PENMAX | f       | (INTEGER)  | INT4       | f       | t      |       1 |             |
INTEGER, INTEGER | NONE    | 0      | ANY     |              |
(1 row)

To list all the aggregates in a database, use the following command. (The output is abbreviated for presentation in the document.)

DEV.SCHEMA(MYUSER)=> SHOW AGGREGATE ALL;
    NAME    | BUILTIN |    ARGUMENTS  | RETURNTYPE | DESCRIPTION
----------- +---------+---------------+------------+-------------
COUNT       | t       | ()            | INT8       |
DENSE_RANK  | t       | ()            | INT8       |
RANK        | t       | ()            | INT8       |
PENMAX      | f       | (INTEGER)     | INT4       |
UDA_SUM     | f       | (INTEGER)     | INT8       |