POSTGRESQL V14’S BETTER SQL FUNCTIONS

An example of an SQL function

Let’s create a simple example of an SQL function with the “classical” syntax so that we have some material for demonstrations:

CREATE EXTENSION unaccent; CREATE FUNCTION mangle(t text) RETURNS text LANGUAGE sql AS 'SELECT lower(unaccent(t))'; 

You can use the new function like other database functions:

SELECT mangle('Schön dumm'); mangle ════════════ schon dumm (1 row) 

Why SQL functions?

You may be wondering what good a SQL function is.In the end, the key benefit of a database function is the ability to execute procedural code within the database, which is not possible with SQL. However, SQL functions have uses as well: 

SQL statements can be made more understandable by factoring out a portion of the code into a function with a meaningful name if a function is required for syntactical reasons, such as in CREATE AGGREGATE or CREATE OPERATOR.

Simple SQL functions can also be “inlined,” which means that during query design, the optimizer can swap out the function call for the function definition. This might make SQL operations incredibly effective.

Since functions are (largely) invisible to the optimizer, replacing the function with its definition typically yields better estimates, eliminating the expense of a real function call.
If we run EXPLAIN (VERBOSE) on our sample code, we can observe function inlining:

EXPLAIN (VERBOSE, COSTS OFF) SELECT mangle('Schön dumm'); QUERY PLAN ═══════════════════════════════════════════════ Result Output: lower(unaccent('Schön dumm'::text)) (2 rows) 

Shortcomings of PostgreSQL functions

The functions in PostgreSQL are excellent. The fact that you are not limited to a single programming language is one of its wonderful features. PostgreSQL by default supports SQL, C, PL/pgSQL (an Oracle PL/SQL clone), Perl, Python, and Tcl functions. But PostgreSQL allows you to create plugins that let you use any language of your choice inside the database, so that’s not all. The body of a PostgreSQL function is just a string constant that the procedural language’s call handler understands when PostgreSQL calls the function, allowing for this flexibility. This has a number of negative consequences:

not tracking dependencies

The pg depend and pg shdepend catalog tables are where PostgreSQL typically keeps track of dependencies between database objects. So, the database will either automatically delete dependent objects or stop you from deleting things that other objects depend on (like a table with a foreign key reference). For example, when you delete a table, all of its indexes are also deleted.

Because a function’s body is just a string constant that PostgreSQL cannot understand. It will not keep track of dependencies between functions and the objects they use. If “check function bodies” is set to “on,” a procedural language can offer a validator. It verifies the syntactic accuracy of the function body. The validator can also check to see if the objects that the function uses are real. However, it can’t stop you from deleting an object that the function used.

Let’s use our example to illustrate that:

DROP EXTENSION unaccent; SELECT mangle('boom'); ERROR: function unaccent(text) does not exist LINE 1: SELECT lower(unaccent(t)) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT lower(unaccent(t)) CONTEXT: SQL function "mangle" during inlining 

The extension will be made once again in order to address the issue. But it would be preferable to receive an error when we execute DROP EXTENSION without the CASCADE option. 

Using search paths poses a security risk.

PostgreSQL uses the current configuration of the search path to resolve all references to database objects that are not qualified with the schema name since it parses the function body at query execution time. That applies to functions and operators in addition to tables and views. We may illustrate the issue using our sample function:

SET search_path = pg_catalog; SELECT public.mangle('boom'); ERROR: function unaccent(text) does not exist LINE 1: SELECT lower(unaccent(t)) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT lower(unaccent(t)) CONTEXT: SQL function "mangle" during inlining 

In our example, utilizing public.unaccent() in the function call will allow us to avoid the minor discomfort. However, it is possible for it to be worse, especially when using Security Definer routines. Because it takes a lot of time to schema-qualify each function and operator, it is suggested that a search path be forced on the function: ​

ALTER FUNCTION mangle(text) SET search_path = public; 

On older versions, the aforementioned is a bad idea because the search path’s schemas should only allow privileged users to CREATE!

A frustrating drawback is that providing a search path prevents the SQL function from being inlined.

The new SQL function syntax in PostgreSQL v14

​The body of SQL functions and procedures no longer has to be a string constant as of PostgreSQL v14. The function body can now be entered using one of the following formats:​

CREATE FUNCTION function_name(...) RETURNS ... RETURN expression; CREATE FUNCTION function_name(...) RETURNS ... BEGIN ATOMIC statement; ... END; 

The function body must be an expression for the first form. Therefore, you must include a query in parentheses if you want to execute it (turning it into a subquery, which is a valid expression). For instance:​

CREATE FUNCTION get_data(v_id bigint) RETURNS text RETURN (SELECT value FROM data WHERE is = v_id); 

You can create a function using more than one SQL statement using the second form. The outcome of the function will be the outcome of the last SQL statement, just like it used to be with multi-statement SQL functions. The second variation of the new syntax can be used to develop SQL procedures. Given that procedures don’t have return values, the first form is obviously inappropriate for one.

To adopt the new syntax, we can simply update our sample function as follows:

CREATE OR REPLACE FUNCTION mangle(t text) RETURNS text RETURN lower(unaccent(t)); 

Keep in mind that, like the previous ones, these new SQL functions can be directly included into SQL statements.

Advantages of the new SQL function syntax

The primary distinction is the parsing of new-style SQL functions and procedures at function definition time and the storage of parsed forms in the prosqlbody column of the pgproc system catalog. As a result, the two issues mentioned above are resolved:

Dependency tracking using modern SQL operations

PostgreSQL can keep track of dependencies since the function body is provided in parsed form. Let’s test that using the example function we redefined:

DROP EXTENSION unaccent; ERROR: cannot drop extension unaccent because other objects depend on it DETAIL: function mangle(text) depends on function unaccent(text) HINT: Use DROP ... CASCADE to drop the dependent objects too. 

The search path was fixed with new-style SQL functions.

When parsing SQL, just the search path is pertinent. We don’t need to worry about the current setting of that argument at function execution time because this now occurs when CREATE FUNCTION runs:

SET search_path = pg_catalog; SELECT public.mangle('Schön besser'); mangle ══════════════ schon besser (1 row) 

Problems with interactive clients

You may notice that the multi-statement form for defining SQL functions contains semicolons to terminate the SQL statements. That will not only confuse the usual suspects like HeidiSQL (which never learned dollar quoting), but it will be a problem for any client that recognizes semicolons as separator between SQL statements. Even older versions of psql have a problem with that syntax:

psql (13.7, server 15beta2) WARNING: psql major version 13, server major version 15. Some psql features might not work. Type "help" for help. test=> CREATE FUNCTION tryme() RETURNS integer BEGIN ATOMIC SELECT 42; END; ERROR: syntax error at end of input LINE 3: SELECT 42; ^ WARNING: there is no transaction in progress COMMIT 

psql thinks that the semicolon after “SELECT 42” terminates the CREATE FUNCTION statement. The truncated statement causes an error. The final END is treated as its own statement, which is a synonym for COMMIT and causes a warning.

In v14 and above, psql handles such statements correctly. pgAdmin 4 has learned the new syntax with version 6.3. But I am sure that there are many clients out there that have not got the message yet.

Conclusion

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​

​