Functions, aggregates, and user types

Query system_schema functions, aggregates, and types tables to get information about user-defined functions, user-defined aggregates, and user-defined types.

Query system_schema functions, aggregates, and types tables to get information about user-defined functions (UDFs), user-defined aggregates (UDAs), and user-defined types (UDTs). UDFs are not supported in Astra.

Tip: Use OpsCenter to view the CQL for defined UDF, UDA, and UDT in a keyspace.

Procedure

  • Show one user-defined function in the system_schema.functions table.
    SELECT *
    FROM system_schema.functions
    LIMIT 1;
    The following output shows the first record using the cqlsh EXPAND ON option.
    @ Row 1
    ----------------------+------------------------------------------------------------------------------------------------------------------
    --------------------------------------------------------
     keyspace_name        | cycling
     function_name        | avgfinal
     argument_types       | ['frozen<tuple<int, bigint>>']
     argument_names       | ['state']
     body                 | \n      double r = 0; \n      if (state.getInt(0) == 0) return null; \n      r = state.getLong(1); \n      r /= s
    tate.getInt(0); \n      return Double.valueOf(r);\n    
     called_on_null_input | True
     deterministic        | False
     language             | java
     monotonic            | False
     monotonic_on         | []
     return_type          | double
    
    (1 rows)
  • List the schema settings for one user-defined aggregate.
    SELECT *
    FROM system_schema.aggregates
    LIMIT 1;
    @ Row 1
    ----------------+----------------------------
     keyspace_name  | cycling
     aggregate_name | average
     argument_types | ['int']
     deterministic  | False
     final_func     | avgfinal
     initcond       | (0, 0)
     return_type    | double
     state_func     | avgstate
     state_type     | frozen<tuple<int, bigint>>
    
    (1 rows)
  • Show the schema settings for one user-defined type.
    SELECT *
    FROM system_schema.types
    LIMIT 1;
    @ Row 1
    ---------------+-------------------------------------------------
     keyspace_name | cycling
     type_name     | basic_info
     field_names   | ['birthday', 'nationality', 'height', 'weight']
     field_types   | ['timestamp', 'text', 'text', 'text']
    
    (1 rows)