Difficulty: Intermediate
Estimated Time: 15 minutes

Getting Started With Functions in PostgreSQL

This class gives you a basic introduction to writing functions which can be used to add functionality to PostgreSQL. We are not going to cover functions in depth, but instead this class will prepare you for some of the core concepts needed in other scenarios.

Functions Are Everywhere in PostgreSQL

PostgreSQL is quite well know for its flexibility and extensibility. One of the ways that it achieves this ability is because almost everything in PostgreSQL is a function. For example, all the base datatypes are actually functions that translate from the underlying binary types to the human readable forms. Another example is the + operators. It is actually a function written in C that takes arguments from both sides of the operator and returns the result.

Here is a list of all the places PostgreSQL uses functions:

  • Functions
  • Operators
  • Data types
  • Index methods
  • Casts
  • Triggers
  • Aggregates
  • Ordered-set Aggregates
  • Window Functions

Stored procedure and triggers are the most common places you will use functions.

Languages to Use When Writing Functions

PostgreSQL has a wide array of programming languages you can use to write functions.

You can use:

  • SQL
  • PL/pgSQL: a procedural language included in PostgreSQL maintained by the PostgreSQL foundation (similar to Oracle's PL/SQL) We will be using mostly SQL with some pgSQL as our main languages for this scenario
  • PL/Tcl
  • PL/Perl
  • PL/Python: Python 2 and 3, with 2 still being the default (unfortunately)
  • PL/V8 (JavaScript)
  • PL/Java
  • PL/R
  • PL/Ruby
  • PL/Lua
  • PL/Shell

Here are the details on the database we are connecting to:

  1. Username: groot
  2. Password: password (same password for the postgres user as well)
  3. A database named: workshop

    Let's get started

Final Notes

NOTE: All the programming langauges we used in these exercises are considered "trusted" by PostgreSQL. Trusted means the language does not give the developer access to the operating system file system and are much harder to do "bad things" with them. Because of this, functions written in trusted langauges can be written and used by non-superusers.

If we had done this class in, say Pl/Python, PostgreSQL would have prevented us from making functions since Python is an untrusted language. While we are not going to cover how to developer with an untrusted in this course, you need to be aware of this restriction.

I hope this scenario helped:

  1. Give you an idea how easy it is to get started with functions in PostgreSQL
  2. Get your hands dirty with some basic functions
  3. You understand how functions can help you produce better applications

The best way to get better at functions is to keep writing them and playing.
The container used in this class is available in Dockerhub. As long as you have Docker on your machine you can use the same version of PostgreSQL as the workshop. All the data from the workshop was intentionally chosen from public domain or permissive licenses so that you can use it for commercial and non-commercial purposes. Feel free to download it and play some more at your own pace on your own machine.

And with that, we are finished.

Basic Functions For Developers

Step 1 of 3

Our First Function

Our First Function

Let's lay a little groundwork so we can write our first function. There are some basic pieces all functions, regardless of programming language, need in order to work.

Basic Pieces of Every Function

Just like functions in other languages, PostgreSQL functions have certain structure and syntax. Let's avoid dealing with parameters for the time being and just make the simplest function possible. We are just going to make a simple function that returns, get ready for it, the string "hello world".

The Declaration - the name and parameters

Let's go to terminal, which is already at the psql interactive prompt. To being with we need to start the opening block of the function and give it a name. Please note the capitalization for SQL reserved words is optional but done here for clarity.

CREATE FUNCTION brilliance()

We are declaring a function named "brilliance" and saying it doesn't accept any parameters. We will return to cover this declaration in more depth later in the scenario.

Return Declaration

We already stated we are going to return a string so let's go ahead and set that up. In later scenarios we will explore other return types.

RETURNS VARCHAR AS

Function Body

Now we can write our function body. We demarcate the begin and end of the code with $$ symbol. We use $$ rather than " or ' so that we don't have to bother escaping strings in our code. When using SQL as our programming language only the last executed line (ending in a ;) will be returned. We also can't use RETURN to specify which result we want to return.

$$
   SELECT 'hello world';
$$

Notice we use the SQL ';' delimeters at the the end of each SQL statement.

Language Specification

Finally, we need to tell PostgreSQL what programming language we used in our function. In this case we are just going to use SQL.

LANGUAGE sql;

Calling our New Function

Now to use our brand new shiny function

select brilliance();

Now any time we want to say "Hello World" in the workshop database all we have to do is call our function. I know this wasn't that exciting yet but hopefully, now you see the basic structure of PostgreSQL function. As mentioned in the intro., functions form the bases for most every extra functionality we want to create, such a stored procedures.

Wrap Up

We just finished the basic skeleton of a function: declaration, function name, parameters, return type, code block, and language used. In our next exercise we will explore doing more with the function declaration and parameters.