Database Reference
In-Depth Information
Structure of a PL/pgSQL function
It doesn't take much to get a PL/pgSQL function working. Here is a very basic ex-
ample:
CREATE FUNCTION mid(varchar, integer, integer)
RETURNS varchar
AS $$
BEGIN
RETURN substring($1,$2,$3);
END;
$$
LANGUAGE plpgsql;
The previous function shows the minimal elements of a PL/pgSQL function. It creates
an alias for the
substring
built-in function called
mid
. This is a handy alias to have
around for developers that come from Microsoft SQL Server or MySQL and are won-
dering what happened to the
mid
function. It also illustrates the most basic parameter
passing strategy. The parameters are not named and are accessed in the function by
relative location from left to right.
The basic elements are name, parameters, return type, body, and language. It could
be argued that parameters are not mandatory for a function and neither is the return
value. This might be useful for a procedure that operates on data without providing a
response, but it would be prudent to return a value of
TRUE
to indicate that the pro-
cedure succeeded.
Accessing function arguments
Function arguments can also be passed and accessed by name, instead of just by
the ordinal order. By accessing the parameters by name, it makes the resulting func-
tion code a little more readable. The following is an example of a function that uses
named parameters:
CREATE FUNCTION mid(keyfield varchar,
starting_point integer) RETURNS varchar
