We all know and value SQL functions as a handy shortcut. PostgreSQL v14 has introduced a new, better way to write SQL functions. This article will show the advantages of the new syntax. An example of an SQL function Let’s create a simple example of an SQL function with the “classical” syntax so that we […]

As CYBERTEC keeps expanding, we need a lot more office space than we previously did. Right now, we have a solution in the works: a new office building. We wanted something beautiful, so we started to dig into mathematical proportions to achieve a reasonable level of beauty. We hoped to make the building not just […]

This blog is about table functions and performance. PostgreSQL is a really powerful database and offers many features to make SQL even more powerful. One of these impressive things is the concept of a composite data type. In PostgreSQL a column can be a fairly complex thing. This is especially important if you want to […]

  PostgreSQL commit 74dfe58a5927b22c744b29534e67bfdd203ac028 has added “support functions”. This exciting new functionality that allows the optimizer some insight into functions. This article will discuss how this will improve query planning for PostgreSQL v12. If you are willing to write C code, you can also use this functionality for your own functions. Functions as “black boxes” […]

Pavel Stehule recently wrote the post “Don’t use SQL keywords as PLpgSQL variable names” describing the situation when internal stored routine variable names match PostgreSQL keywords. But the problem is not only in keywords but also for plpgsql variable names. Consider: Output: OK, at least we have no hidden error like in Pavel’s case. Let’s […]

Recently I did some PostgreSQL consulting in the Berlin area (Germany) when I stumbled over an interesting request: How can data be shared across function calls in PostgreSQL? I recalled some of the older features of PostgreSQL (15+ years old or so) to solve the issue. Here is how it works. Stored procedures in PostgreSQL […]

SQL is a must, if you want to be a Data Analyst or a Data Scientist. However, every once in a while people wonder why a result is the way it is. While on the road in Berlin (Germany) the other day, I found a fairly interesting window function scenario which is pretty counter-intuitive to […]

It is possible to write functions in PostgreSQL in almost any widespread language such as Perl, Python or C. In general this offers a great deal of flexibility and acceptable performance. However, in some cases customers might say: “We have the feeling that procedures are slow”. The thing is: PostgreSQL might not be to blame […]

Many people who have used PostgreSQL or SQL in general in a professional or semi-professional way have stumbled across “aggregate functions”. A database engine such as PostgreSQL usually provides the most basic aggregate functions such as count, min, max, sum, and so on. However, those functions are pretty limited and fulfil only the basic needs […]

PostgreSQL has offered support for powerful analytics and window functions for a couple of years now. Many people all around the globe use analytics to make their applications more powerful and even faster. However, there is a small little feature in the area of analytics which is not that widely known. The power to use composite […]

PostgreSQL 9.3 has just been released and we have already received a lot of positive feedback for the new release. Many people are impressed by what has been achieved recently and are already eager to enjoy those new features. As always, the new release brings a great deal of new functionality and many improvements.

It happens quite frequently that people complain about stored procedure performance in PostgreSQL. In many cases the reason for bad performance can be explained quite nicely when looking at the definition of a function. In general, a PostgreSQL function can be marked as follows: VOLATILE, STABLE, IMMUTABLE or [NOT] LEAKPROOF. What does this actually mean? […]