Writing Stored Procedures

In Chapter 6, Optimizing Queries for Good Performance, we learned a lot about the optimizer, as well as the optimizations going on in the system. In this chapter, we will learn about stored procedures and how to use them efficiently and easily. You will learn about what a stored procedure is made up of, which languages are available, and how you can speed things up nicely. On top of that, you will be introduced to some of the more advanced features of PL/pgSQL, and you will learn how to write good server-side code.

The following topics will be covered in this chapter:

• Understanding stored procedure languages
• Exploring various stored procedure languages
• Improving functions
• Using functions for various purposes

By the end of this chapter, you will be able to write good, efficient stored procedures.

Understanding stored procedure languages

When it comes to stored procedures and functions, PostgreSQL differs quite significantly from other database systems. Most database engines force you to use a certain programming language to write server-side code. Microsoft SQL Server offers Transact-SQL, while Oracle encourages you to use PL/SQL. PostgreSQL doesn’t force you to use a certain language; instead, it allows you to decide on what you know and like the best.

The reason PostgreSQL is so flexible is actually quite interesting in a historical sense, too. Many years ago, one of the most well-known PostgreSQL developers, Jan Wieck, who had written countless patches back in its early days, came up with the idea of using Tool Command Language (Tcl) as the server-side programming language. The trouble was that nobody wanted to use Tcl, and nobody wanted to have this stuff in the database engine. The solution to the problem was to make the language interface so flexible that basically...

Exploring various stored procedure languages

As we’ve already stated in this chapter, PostgreSQL gives you the power to write functions and store procedures in various languages. The following options are available and are shipped along with the PostgreSQL core:

• SQL
• PL/pgSQL
• PL/Perl and PL/PerlU
• PL/Python
• PL/Tcl and PL/TclU

SQL is the obvious choice for writing functions, and it should be used whenever possible, as it gives the most freedom to the optimizer. However, if you want to write slightly more complex code, PL/pgSQL might be the language of your choice.

PL/pgSQL offers flow control and much more. In this chapter, some of the more advanced and lesser-known features of PL/pgSQL will be shown, but do keep in mind that this chapter is not meant to be a complete tutorial on PL/pgSQL.

The core contains code to run server-side functions in Perl. Basically, the logic is the same here. Code will be passed as a string and executed by Perl....

Improving functions

So far, you have seen how to write basic functions and triggers in various languages. Of course, many more languages are supported. Some of the most prominent ones are PL/R (R is a powerful statistics package) and PL/v8 (which is based on the Google JavaScript engine). However, those languages are beyond the scope of this chapter (regardless of their usefulness).

In this section, we will focus on improving the performance of a function. There are a few ways by which we can speed up processing:

• Reducing the number of function calls
• Using cached plans
• Giving hints to the optimizer

In this section, all three of these topics will be discussed. Let’s get started with reducing the number of function calls and see how this can be done.

Reducing the number of function calls

In many cases, performance is bad because functions are called way too often. In my opinion—and I cannot stress this point enough—calling things too...

Using functions for various purposes

In PostgreSQL, stored procedures can be used for pretty much everything. In this chapter, you have already learned about the CREATE DOMAIN clause and so on, but it is also possible to create your own operators, type casts, and even collations.

In this section, you will see how a simple type cast can be created and how it can be used to your advantage. To define a type cast, consider taking a look at the CREATE CAST clause. The syntax of this command is shown in the following code:

test=# \h CREATE CAST
Command:    CREATE CAST
Description: define a new cast
Syntax:
CREATE CAST (source_type AS target_type)
   WITH FUNCTION function_name [ (argument_type [, ...]) ]
   [ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
   WITHOUT FUNCTION
   [ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
   WITH INOUT
...

Summary

In this chapter, you have learned how to write stored procedures. After a theoretical introduction, our attention was focused on some selected features of PL/pgSQL. In addition to that, you learned how to use PL/Perl and PL/Python, which are two important languages that PostgreSQL provides. Of course, there are many more languages available. However, due to the limitations of the scope of this book, they could not be covered in detail. If you want to know more, check out the following website: https://wiki.postgresql.org/wiki/PL_Matrix. We also learned how to improve function calls and how we can use them for various other purposes to speed up applications and do a lot more.

In Chapter 8, Managing PostgreSQL Security, you will learn about PostgreSQL security. You will learn how to manage users and permissions in general, and on top of that, you will also learn about network security.

Questions

1. What is the difference between a function and a stored procedure?
2. What is the difference between a trusted and an untrusted language?
3. In general, are functions good or bad?
4. Which server-side languages are available in PostgreSQL?
5. What is a trigger?
6. Which languages can be used to write functions?
7. Which language is the fastest?

Answers to these questions can be found in the GitHub repository (https://github.com/PacktPublishing/Mastering-PostgreSQL-15-).