As it turns out, part of defining a new type is the definition
of functions that describe its behavior. Consequently, while it is
possible to define a new function without defining a new type, the
reverse is not true. We therefore describe how to add new functions
to Postgres before describing how
to add new types. PostgresSQL provides two types of functions:
query language functions (functions written in SQL and programming language functions (functions
written in a compiled programming language such as C.) Either kind of function can take a base type,
a composite type or some combination as arguments (parameters). In
addition, both kinds of functions can return a base type or a
composite type. It's easier to define SQL functions, so we'll start with those. Examples
in this section can also be found in funcs.sql and funcs.c.

Notice that we defined a target list for the function (with the
name RESULT), but the target list of the query that invoked the
function overrode the function's target list. Hence, the result is
labelled answer instead of one.

It's almost as easy to define SQL
functions that take base types as arguments. In the example below,
notice how we refer to the arguments within the function as $1 and
$2.

When specifying functions with arguments of composite types
(such as EMP), we must not only specify which argument we want (as
we did above with $1 and $2) but also the attributes of that
argument. For example, take the function double_salary that
computes what your salary would be if it were doubled.

Notice the use of the syntax $1.salary. Before launching into
the subject of functions that return composite types, we must first
introduce the function notation for projecting attributes. The
simple way to explain this is that we can usually use the notation
attribute(class) and class.attribute interchangably.

As we shall see, however, this is not always the case. This
function notation is important when we want to use a function that
returns a single instance. We do this by assembling the entire
instance within the function, attribute by attribute. This is an
example of a function that returns a single EMP instance:

In this case we have specified each of the attributes with a
constant value, but any computation or expression could have been
substituted for these constants. Defining a function like this can
be tricky. Some of the more important caveats are as follows:

The target list order must be exactly the same as that in
which the attributes appear in the CREATE TABLE statement (or
when you execute a .* query).

You must typecast the expressions (using ::) very carefully
or you will see the following error:

WARN::function declared to return type EMP does not retrieve (EMP.*)

When calling a function that returns an instance, we cannot
retrieve the entire instance. We must either project an
attribute out of the instance or pass the entire instance into
another function.

The reason why, in general, we must use the function syntax
for projecting attributes of function return values is that the
parser just doesn't understand the other (dot) syntax for
projection when combined with function calls.

SELECT new_emp().name AS nobody;
WARN:parser: syntax error at or near "."

Any collection of commands in the SQL query language can be packaged together and
defined as a function. The commands can include updates (i.e.,
insert, update and delete) as
well as select queries. However, the
final command must be a select that
returns whatever is specified as the function's returntype.