Server-Side Programming

In previous chapters, we learned how to execute SQL queries. We started by writing simple queries, then moved on to writing more complex queries; we learned how to use aggregates in the traditional way, and in Chapter 5, Advanced Statements, we talked about window functions, which are another way to write aggregates. In this chapter, we will add server-side programming to this list of skills. Server-side programming can be useful in many cases as it moves the programming logic from the client side to the database side. For example, we could use it to take a function that has been written many times at different points of the application program and move it inside the server so that it is written only once, meaning that in case of modification, we only have to modify one function. In this chapter, we will also look at how PostgreSQL can manage different...

Exploring data types

As users, we have already had the opportunity to experience the power and versatility of server-side functions – for example, in Chapter 5, Advanced Statements, we used a query similar to the following:

forumdb=# select * from categories where upper(title) like 'A%';
 pk | title   | description 
----+---------+-------------
 10 | apple   | fruits
 14 | apricot | fruits
(2 rows)

In this piece of code, the upper function is a server-side function; this function turns all the characters of a string into uppercase. In this chapter, we will acquire the knowledge to be able to write functions such as the upper functions that we called in the preceding query.

In this section, we'll talk about data types. We will briefly mention the standard types managed by PostgreSQL and how to create new ones.

The concept of extensibility

What is extensibility? Extensibility is PostgreSQL's ability to extend its functionality and its data types. Extensibility is...

Exploring functions and languages

PostgreSQL is capable of executing server-side code. There are many ways to provide PostgreSQL with the code to be executed. For example, the user can create functions in different programming languages. The main languages ​​supported by PostgreSQL are as follows:

• SQL
• PL/pgSQL
• C

These listed languages are the built-in languages; there are also other languages that PostgreSQL can manage, but before using them, we need to install them on our system. Some of these other supported languages ​​are as follows:

• PL/Python
• PL/Perl
• PL/tcl
• PL/Java

In this section, we'll talk about SQL and PL/pgSQL functions.

Functions

The command structure with which a function is defined is as follows:

CREATE FUNCTION function_name(p1 type, p2 type,p3 type, ....., pn type)
 RETURNS type AS
BEGIN
 -- function logic
END;
LANGUAGE language_name

The following steps always apply for any type of function we want to create:

1. Specify the name of the function...

Summary

In this chapter, we introduced the world of server-side programming. The topic is so vast that there are specific books dedicated just to server-side programming. We have tried to give you a better understanding of the main concepts of server-side programming. We talked about the main data types managed by PostgreSQL, then we saw how it is possible to create new ones using composite data types. We also mentioned SQL functions and polymorphic functions, and finally, provided some information about the PL/pgSQL language.

In the next chapter, we will use these concepts to introduce event management in PostgreSQL. We will talk about event management through the use of triggers and the functions associated with them.

References

• Postgresql 12 – data types official documentation: https://www.postgresql.org/docs/12/datatype.html
• Postgresql 12 – SQL functions official documentation: https://www.postgresql.org/docs/12/xfunc-sql.html
• Postgresql 12 – PL/PGSQL official documentation: https://www.postgresql.org/docs/12/plpgsql.html