Already know some SQL basics and want to take your PostgreSQL skills to the next level? Learn how to create, use, and manage your own functions in PostgreSQL.

We’re pleased to announce the launch of our new online course: Writing User-Defined Functions in PostgreSQL.

This course is aimed at people who already know some basic SQL and greatly benefits not only PostgreSQL developers and database administrators but also anyone interested in database programming (especially through PostgreSQL).

Like the others, this course doesn’t just focus on theoretical concepts—you’ll learn by doing. Every single bit of theory has its own practical part full of real-world exercises.

If you’d prefer to start with some SQL basics first, be sure to try out our existing online courses: SQL Basics, SQL Practice Set, and SQL Joins.

Why Write User-Defined Functions in PostgreSQL?

User-defined functions are one of the most crucial database objects, and every database developer should know how to write, execute, and manage them properly. Don't be surprised if you see the term “stored procedures” refer to a user-defined function in Postgres. Prior to PostgreSQL 11, user-defined functions were often called stored procedures, but with the PostgreSQL 11, user-defined procedures have been introduced. According to the Postgres documentation, the difference is that a procedure does not return a value. While a function is called as part of a query or DML command, a procedure is called explicitly using the CALL statement.

What are the real benefits of writing your own functions, and why should you learn more about this topic?

First and foremost, functions extend the functionality of a database. This means you are not limited to just using Postgres’s built-in functions; you can develop and manage your own functions that can be reused and invoked from various scripts.

In Postgres, functions also allow you to encapsulate your operations (business logic) that can be invoked like a programming subroutine. Imagine you have to perform some specific operations in your SQL code fairly often, using some complex patterns, formulas, etc. With user-defined functions, you can encapsulate this custom logic, parametrize it, and invoke the function from your code when required.

Another big advantage of functions is code reusability—imagine that you have to repeat certain calculations in your SQL queries. Instead of rewriting those calculations each time, you can just simply create a function that performs those calculations and can later be called from a query.

In the beginning of this online course, you’ll learn what a user-defined function is, how to create one, and when you should use one.

The course also covers the syntax of PostgreSQL’s most commonly used procedural language, PL/pgSQL.

You will also learn all the different types of parameters in PostgreSQL functions: IN, OUT, and INOUT.

Last but not least, you’ll get familiar with using the results of an SQL query inside a function, nesting function calls, and returning a table from a function. These patterns will be useful when programming real-world solutions. For instance, nesting function calls is a common pattern when implementing some master-slave or parent-child scenarios.

What’s in It for Me?

• 94 interactive exercises. Learn at your own pace, from anywhere and anytime. Interact with hands-on exercises for improved retention.
• Lifetime access to the course. When you purchase the course, you'll get instant personal access to all of its content.
• Certificate of completion. After you successfully finish all of the exercises, you'll get a downloadable PDF certificate to showcase your accomplishment.
• 30-day money-back guarantee. If you're not satisfied with the quality of the course, you can get a refund within 30 days of your purchase.
• Hints for the exercises. You can ask questions and share insights with other members of the Vertabelo Academy community through the Discuss tab. You can also drop us a line directly at academy@vertabelo.com, and we'll be more than happy to answer!

Sign up for this amazing online course and upskill yourself with Vertabelo Academy today! And remember: Education is the key to success in life.