Difficulty: Intermediate
Estimated Time: 15 minutes

Getting Started With Functions in PostgreSQL

This class helps you understand how to interpret and use EXPLAIN with your PostgreSQL queries. EXPLAIN (and EXPLAIN ANALYZE) show you the planned (and actual) solution the PostgreSQL query planner uses to satisfy your query.

Query Planner




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.

Using Explain to Write Better Queries

Step 1 of 3

Understand Explain Output

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.


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.


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.