It doesn't take much to get a PL/pgSQL function working. Here's a basic example:
CREATE FUNCTION mid(varchar, integer, integer) RETURNS varchar AS $$ BEGIN RETURN substring($1,$2,$3); END; $$ LANGUAGE plpgsql;
The preceding function shows the basic 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 wondering what happened to the mid
function. It also illustrates the most basic parameter-passing strategy: parameters are not named and are accessed in the function by their relative location from left to right. The $$
character in this example represents the start and end of the code block. This character sequence can be arbitrary and you can use something else of your choice, but this book uses $$
in all the examples.
The basic elements of a PL/pgSQL function are name, parameters, return type, body, and...