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:
- Data types
- Index methods
- 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:
- 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/Python: Python 2 and 3, with 2 still being the default (unfortunately)
Here are the details on the database we are connecting to:
- Username: groot
- Password: password (same password for the postgres user as well)
A database named: workshop
Let's get started
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:
- Give you an idea how easy it is to get started with functions in PostgreSQL
- Get your hands dirty with some basic functions
- 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.
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.
Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.
Basic Functions For Developers
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.
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
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 ';' delimiters at the end of each SQL statement.
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
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.
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.