O'Reilly logo

PostgreSQL Server Programming by Jim Mlodgenski, Kirk Roybal, Hannu Krosing

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Structure of a PL/pgSQL function

It doesn't take much to get a PL/pgSQL function working. Here is a very basic example:

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 wondering 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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required