Implementing Analytics with Greenplum UAP - Getting Started with Greenplum for Big Data Analytics - page 146

• Create a function with the CREATE FUNCTION command. You must have
CREATE access to the schema to create a function. A function can be created
with or without parameters.
• Replace an existing function with the CREATE OR REPLACE FUNCTION
command. This command either creates a function if one did not exist before,
or replaces an existing function. If you are replacing an existing function, you
must specify the same number of parameters and the same data types found
in the original function. If not, you are actually creating a new function.
• Change a function with the ALTER FUNCTION command. You must own the
function before you can modify it. If the function is to be created in another
schema, you must have CREATE privilege on that schema.
• Drop or remove a function with the DROP FUNCTION command. Because
you can have multiple functions with the same name but different number of
parameters and/or parameter types, you must include the appropriate num-
ber of parameters and parameter types as part of the command. You must
also be the owner of the function to remove the function from the schema.
User-defined aggregates
User-defined aggregates perform a single table scan and it keeps state. A state is a
maximum of two numbers. In this example we create a user-defined aggregate that
returns a maximum of two numbers.
CREATE AGGREGATE defines a new aggregate function. Some basic and commonly
used aggregate functions such as count , min , max , sum , avg , and so on are
already provided in the Greenplum Database.
If one defines new types or needs an aggregate function not already provided, then
CREATE AGGREGATE can be used to provide the desired features.
An aggregate function is made from one, two, or three ordinary functions (mutually
exclusive): a state transition function sfunc , an optional preliminary segment-level
calculation function prefunc , and an optional final calculation function ffunc .
These are used as follows:
• sfunc( internal-state, next-data-values ) ---> next-
internal-state