What SQL functions are for
SQL functions are more than a way to run procedural code in the database. They exist to reuse expressions across statements, to give a meaningful name to a fragment of SQL and make the surrounding statement readable, and to satisfy syntactic requirements such as CREATE AGGREGATE or CREATE OPERATOR.
They can also be remarkably fast: simple SQL functions may be inlined, meaning the optimizer substitutes the function definition for the call at planning time. That eliminates the overhead of a real function call and typically yields better estimates, since a function is (mostly) a black box to the optimizer. EXPLAIN (VERBOSE) shows the substitution:
|
1 2 3 4 5 6 7 |
EXPLAIN (VERBOSE, COSTS OFF) SELECT mangle('Schön dumm'); QUERY PLAN ═══════════════════════════════════════════════ Result Output: lower(unaccent('Schön dumm'::text)) (2 rows) |
Why the classic body is a problem
PostgreSQL supports functions in SQL, C, PL/pgSQL, Perl, Python and Tcl, and a plugin can add any other language. To permit that flexibility, a function body has historically been a string constant that the language's call handler interprets at execution time. Two consequences follow.
Dependencies go untracked
PostgreSQL records relationships between objects in pg_depend and pg_shdepend, and uses them to block a drop or to cascade it. Because the body is a string the server cannot interpret, dependencies between the function and the objects it references are not recorded. A language may supply a validator that checks the body syntactically (when check_function_bodies = on) and even verifies that referenced objects exist, but nothing stops you from dropping an object the function later needs.
|
1 2 3 4 5 6 7 8 9 |
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 |
Recreating the extension repairs the situation. An error on DROP EXTENSION without CASCADE would be preferable.
search_path resolves at run time
Because parsing happens at execution time, unqualified references — to tables and views, but also to functions and operators — are resolved with whatever search_path is current then.
|
1 2 3 4 5 6 7 8 9 |
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 the example, qualifying the call as public.unaccent() removes the annoyance. With SECURITY DEFINER functions the exposure is considerably worse. Since schema-qualifying every function and operator is impractical, the standard remedy is to pin a search_path on the function:
|
1 |
ALTER FUNCTION mangle(text) SET search_path = public; |
Schemas on that search_path must grant CREATE only to privileged users — on versions older than v15 this is not a safe arrangement. The cost of pinning a search_path is that it blocks inlining of the SQL function.
PostgreSQL v14: bodies that are no longer strings
Beginning with v14, the body of an SQL function or procedure need not be a string constant. Either of these forms is accepted:
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION function_name(...) RETURNS ... RETURN expression; CREATE FUNCTION function_name(...) RETURNS ... BEGIN ATOMIC statement; ... END; |
The first form takes an expression, so a query must be parenthesized to become a subquery:
|
1 2 |
CREATE FUNCTION get_data(v_id bigint) RETURNS text RETURN (SELECT value FROM data WHERE is = v_id); |
The second form holds more than one SQL statement; as with multi-statement SQL functions, the function returns the result of the last statement. That form also works for creating SQL procedures, whereas the expression form does not, since procedures return nothing. Rewriting the earlier example is straightforward:
|
1 2 |
CREATE OR REPLACE FUNCTION mangle(t text) RETURNS text RETURN lower(unaccent(t)); |
These new-style functions inline into SQL statements just as the old ones did.
Definition-time parsing, and what it buys
The decisive change is that new-style SQL functions and procedures are parsed when they are defined and stored in parsed form in the prosqlbody column of pg_proc. Both shortcomings disappear.
Dependencies become trackable, since the body is available parsed:
|
1 2 3 4 |
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. |
And search_path stops mattering at execution time, because it is only consulted when SQL is parsed — which now happens during CREATE FUNCTION:
|
1 2 3 4 5 6 7 8 |
SET search_path = pg_catalog; SELECT public.mangle('Schön besser'); mangle ══════════════ schon besser (1 row) |
Client support
The multi-statement form terminates its SQL statements with semicolons, which confuses any client that treats a semicolon as a statement separator, not just tools like HeidiSQL that never handled dollar quoting. Older psql versions misread the statement:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 takes the semicolon after SELECT 42 as the end of CREATE FUNCTION, produces an error on the truncated statement, and then treats the trailing END as a standalone statement — a synonym for COMMIT, hence a warning. From v14 on, psql handles the syntax correctly, and pgAdmin 4 learned it in version 6.3. Other clients may still lag behind.
Conclusion
The syntax PostgreSQL v14 introduced for SQL functions improves both usability and security. Use a client that supports it, adopt it for new SQL functions, and consider rewriting existing ones to collect the benefits.
