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 server-side programming languages, and we will see that server-side programming can be very useful if you...

Technical requirements

Before starting, remember to start the Docker container named chapter_07, as shown below:

$ bash run-pg-docker.sh chapter_07
postgres@learn_postgresql:~$ psql -U forum forumdb

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    
----+-------+------------------------------
  4 | A.I   | Machine Learning discussions
(1 row)

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 function 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...

The NoSQL data type

In this section, we will approach the NoSQL data types that are present in PostgreSQL. Since this book is not specifically focused on NoSQL, we will just take a quick look.

PostgreSQL handles the following NoSQL data types:

• hstore
• xml
• json/jsonb

We will now talk about hstore and json.

The hstore data type

hstore was the first NoSQL data type that was implemented in PostgreSQL. This data type is used for storing key-value pairs in a single value. Before working with the hstore data type, we need to enable the hstore extension on our server:

forumdb=> create extension hstore ;
CREATE EXTENSION

Let’s look at how we can use the hstore data type with an example. Suppose that we want to show all posts with their usernames and their categories:

forumdb=> select p.pk,p.title,u.username,c.title as category
from posts p
inner join users u on p.author=u.pk
left join categories c on p.category=c.pk
order...

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 to any type...

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 it. 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, we 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.

Verify your knowledge

• Is it possible to extend Is it possible to extend features and data types in postgresql?

  Yes it is, we can extend PostgreSQL in terms of data types and in terms of functions.

  See the The concept of extensibility section for more details.

• Does PostgreSQL support only relational databases?

  No, PostgreSQL supports NoSQL databases too.

  See the The NoSql data type section for more details.

• Does PostgreSQL support SQL functions?

  Yes it does, we can write any kind of SQL function.

  See the SQL functions section for more details.

• Does PostgreSQL have a default built-in procedural language ?

  Yes PostgreSQL has a default built-in procedural language called PL/pgSQL.

  See the PL/pgSQL functions section for more details.

• As a user without administrative privileges, can we read a table that requires administrative permissions in order to be read...

References

• PostgreSQL – data types official documentation: https://www.postgresql.org/docs/current/datatype.html
• PostgreSQL – SQL functions official documentation: https://www.postgresql.org/docs/current/xfunc-sql.html
• PostgreSQL – PL/pgSQL official documentation: https://www.postgresql.org/docs/current/plpgsql.html
• PostgreSQL 11 Server Side Programming Quick Start Guide: https://subscription.packtpub.com/book/data/9781789342222/1

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/jYWCjF6Tku