Description

CREATE FUNCTION defines a new function. CREATE
OR REPLACE FUNCTION will either create a new function, or replace
an existing definition.

The name of the new function must not match any existing function
with the same argument types in the same schema. However, functions of
different argument types may share a name (overloading).

To update the definition of an existing function, use CREATE
OR REPLACE FUNCTION. It is not possible to change the name or
argument types of a function this way (this would actually create a new,
distinct function). Also, 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. Use CREATE OR REPLACE
FUNCTION to change a function definition without breaking objects
that refer to the function.

For more information about creating functions, see the User Defined Functions section of the PostgreSQL
documentation.

Limited Use of VOLATILE and STABLE Functions

To prevent data from becoming out-of-sync across the segments in Greenplum
Database, any function classified as STABLE or VOLATILE
cannot be executed at the segment level if it contains SQL or modifies
the database in any way. For example, functions such as random()
or timeofday() are not allowed to execute on distributed
data in Greenplum Database because they could potentially cause inconsistent
data between the segment instances.

To ensure data consistency, VOLATILE and STABLE
functions can safely be used in statements that are evaluated on and
execute from the master. For example, the following statements are always
executed on the master (statements without a FROM clause):

SELECT setval('myseq', 201);
SELECT foo();

In cases where a statement has a FROM clause
containing a distributed table and the function used
in the FROM clause simply returns a set of
rows, execution may be allowed on the segments:

SELECT * FROM foo();

One exception to this rule are functions that return a table reference
(rangeFuncs) or functions that use the refCursor
data type. Note that you cannot return a refcursor from
any kind of function in Greenplum Database.

Parameters

name

The name (optionally schema-qualified) of the function to create.

argmode

The mode of an argument: either IN, OUT,
or INOUT. If omitted, the default is IN.

argname

The name of an argument. Some languages (currently only PL/pgSQL)
let you use the name in the function body. For other languages the name
of an input argument is just extra documentation. But the name of an
output argument is significant, since it defines the column name in the
result row type. (If you omit the name for an output argument, the system
will choose a default column name.)

argtype

The data type(s) of the function's arguments (optionally schema-qualified),
if any. The argument types may be base, composite, or domain types, or
may reference the type of a table column.

Depending on the implementation language it may also be allowed to
specify pseudotypes such as cstring. Pseudotypes indicate
that the actual argument type is either incompletely specified, or outside
the set of ordinary SQL data types.

The type of a column is referenced by writing
tablename.columnname%TYPE. Using this
feature can sometimes help make a function independent of changes to the
definition of a table.

rettype

The return data type (optionally schema-qualified). The return type
can be a base, composite, or domain type, or may reference the type of
a table column. Depending on the implementation language it may also
be allowed to specify pseudotypes such as cstring. If
the function is not supposed to return a value, specify void
as the return type.

When there are OUT or INOUT parameters,
the RETURNS clause may be omitted. If present, it must
agree with the result type implied by the output parameters: RECORD
if there are multiple output parameters, or the same type as the single
output parameter.

The SETOF modifier indicates that the function will
return a set of items, rather than a single item.

The type of a column is referenced by writing
tablename.columnname%TYPE.

langname

The name of the language that the function is implemented in. May be SQL,
C, internal, or the name of a
user-defined procedural language. See CREATE LANGUAGE for the procedural languages supported in Greenplum
Database. For backward compatibility, the name may be enclosed by single
quotes.

IMMUTABLE

STABLE

VOLATILE

These attributes inform the query optimizer about the behavior of
the function. At most one choice may be specified. If none of these appear,
VOLATILE is the default assumption. Since Greenplum
Database currently has limited use of VOLATILE functions,
if a function is truly IMMUTABLE, you must declare it
as so to be able to use it without restrictions.

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().

CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

CALLED ON NULL INPUT (the default) indicates that
the function will be called normally when some of its arguments are null.
It is then the function author's responsibility to check for null values
if necessary and respond appropriately. RETURNS NULL ON NULL
INPUT or STRICT indicates that the function
always returns null whenever any of its arguments are null. If this parameter
is specified, the function is not executed when there are null arguments;
instead a null result is assumed automatically.

[EXTERNAL] SECURITY INVOKER

[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER (the default) indicates that the
function is to be executed with the privileges of the user that calls
it. SECURITY DEFINER specifies that the function is
to be executed with the privileges of the user that created it. The key
word EXTERNAL is allowed for SQL conformance, but it
is optional since, unlike in SQL, this feature applies to all functions
not just external ones.

definition

A string constant defining the function; the meaning depends on the
language. It may be an internal function name, the path to an object
file, an SQL command, or text in a procedural language.

obj_file, link_symbol

This form of the AS clause is used for dynamically
loadable C language functions when the function name in the C language
source code is not the same as the name of the SQL function. The string
obj_file is the name of the file containing the dynamically loadable
object, and link_symbol is the name of the function in the C language
source code. If the link symbol is omitted, it is assumed to be the same
as the name of the SQL function being defined. It is recommended to locate
shared libraries either relative to $libdir (which is
located at $GPHOME/lib) or through the dynamic library
path (set by the dynamic_library_path server configuration
parameter). This simplifies version upgrades if the new installation
is at a different location.

describe_function

The name of a callback function to execute when a query that calls
this function is parsed. The callback function returns a tuple descriptor
that indicates the result type.

Notes

Any compiled code (shared library files) for custom functions must be
placed in the same location on every host in your Greenplum Database
array (master and all segments). This location must also be in the LD_LIBRARY_PATH
so that the server can locate the files. It is recommended to locate
shared libraries either relative to $libdir (which is
located at $GPHOME/lib) or through the dynamic library
path (set by the dynamic_library_path server configuration
parameter) on all master segment instances in the Greenplum array.

The full SQL type syntax is allowed for input arguments and return
value. However, some details of the type specification (such as the precision
field for type numeric) are the responsibility of the underlying
function implementation and are not recognized or enforced by the CREATE
FUNCTION command.

Greenplum Database allows function overloading. The same name can
be used for several different functions so long as they have distinct
argument types. However, the C names of all functions must be different,
so you must give overloaded C functions different C names (for example,
use the argument types as part of the C names).

Two functions are considered the same if they have the same names
and input argument types, ignoring any OUT parameters. Thus for example
these declarations conflict:

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

When repeated CREATE FUNCTION calls refer to the same
object file, the file is only loaded once. To unload and reload the file,
use the LOAD command.

To be able to define a function, the user must have the USAGE
privilege on the language.

It is often helpful to use dollar quoting to write the function definition
string, rather than the normal single quote syntax. Without dollar quoting,
any single quotes or backslashes in the function definition must be escaped
by doubling them. A dollar-quoted string constant consists of a dollar
sign ($), an optional tag of zero or more characters,
another dollar sign, an arbitrary sequence of characters that makes up
the string content, a dollar sign, the same tag that began this dollar
quote, and a dollar sign. Inside the dollar-quoted string, single quotes,
backslashes, or any character can be used without escaping. The string
content is always written literally. For example, here are two different
ways to specify the string "Dianne's horse" using dollar quoting:

$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$

Using Functions With Queries on Distributed Data

In some cases, Greenplum Database does not support using functions in
a query where the data in a table specified in the FROM
clause is distributed over Greenplum Database segments. As an example,
this SQL query contains the function func():

SELECT func(a) FROM table1;

The function is not supported for use in the query if all of the following
conditions are met:

The data of table table1 is distributed over Greenplum
Database segments.

The function func() reads or modifies data from distributed
tables.

The function func() returns more than one row or takes
an argument (a) that comes from table1.

If any of the conditions are not met, the function is supported. Specifically,
the function is supported if any of the following conditions apply:

The function func() does not access data from distributed
tables, or accesses data that is only on the Greenplum Database master.

The table table1 is a master only table.

The function func() returns only one row and only takes
input arguments that are constant values. The function is supported if
it can be changed to require no input arguments.