creating a function in Greenplum

Note: CREATE OR REPLACE FUNCTION will not let you change the return type of an existing function. To do that, you must drop and recreate the function. If you drop and then recreate a function, you will have to drop existing objects (rules, views, triggers, and so on) that refer to the old 

function.

CREATE [OR REPLACE] FUNCTION name

( [ [argmode] [argname] argtype [, ...] ] )

[ RETURNS { [ SETOF ] rettype

| TABLE ([{ argname argtype | LIKE other table }

[, ...]])

} ]

{ LANGUAGE langname

| IMMUTABLE | STABLE | VOLATILE

| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

| [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER

| AS 'definition'

| AS 'obj_file', 'link_symbol' } ...

[ WITH ({ DESCRIBE = describe_function

} [, ...] ) ]

ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] )

action [, ... ] [RESTRICT]

ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] )

RENAME TO new_name

ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] )

OWNER TO new_owner

ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] )

SET SCHEMA new_schema

where action is one of:

{CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT}

{IMMUTABLE | STABLE | VOLATILE}

{[EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER}

DROP FUNCTION [IF EXISTS] name ( [ [argmode] [argname] argtype

[, ...] ] ) [CASCADE | RESTRICT]

IMMUTABLE => Indicates that the function cannot modify the database and always returns the same result when given the same argument values. It does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

STABLE =>  Indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter values (such as the current time zone), and so on. Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

VOLATILE => Indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().