It's beer o'clock for PostgreSQL

A couple days ago Devrim forked PostgreSQL to get a beer.

That effort is of course laudable, but a bit extreme.

PostgreSQL is very extensible, and allows to implement a beer now clock with just a few easy steps.

Let’s walk through this.

The Schema

Devrim is using the NOW() function in his forked version, which normally returns the current time. Functions in PostgreSQL can also be overloaded, and different functions using the same name can be placed in different schemas. That’s what I am doing here: I create a new NOW() function in whatever schema comes first in search_path. But there is a catch: PostgreSQL places all system functions in a schema named pg_catalog - and this schema, by default, is first in search_path. Unless specified otherwise.

To break this down: pg_catalog is always included in search_path, even if you don’t specify it. If you update this variable, and do not specifically include pg_catalog, then this schema is always the first one. If you however explicitly mention pg_catalog in search_path, then PostgreSQL is using your defined search order.

This has consequences: if you run a SELECT function() query, PostgreSQL will - by default - first search function() in pg_catalog, and then in public. Therefore SELECT NOW() will first pick the now() function which is pre-defined. Let’s change that:

1

SET search_path TO public, pg_catalog;

Now public schema comes before pg_catalog.

The Beer/Beverage Function

After the schema problem is solved, it’s time to create the actual function. And while I am at it, I can as well make the function more flexible, and return everyone’s favorite beverage. For myself, as example, I rather go with a good whishy. I add an optional argument for the beverage, which defaults to “beer”. Also this function is immutable, and it is safe to order multiple beers in parallel. However this comes at a cost.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE OR REPLACE FUNCTION now(beverage TEXT = 'beer')
        RETURNS TEXT
AS $$
    BEGIN

    RETURN 'It''s ' || TRIM(BOTH '''' FROM quote_literal(beverage)) || ' o''clock!';

    END;
$$
LANGUAGE plpgsql
IMMUTABLE
COST 42
PARALLEL SAFE;

Since no schema is specified, this function is placed in the first schema in search_path: public. Exactly where I want it.

Let’s call for a beer:

1
2
3
4
5

beer=# SELECT NOW();
         now
----------------------
 It's 'beer' o'clock!
(1 row)

And for any other beverage:

1
2
3
4
5

beer=# SELECT NOW('whisky');
         now
----------------------
 It's whisky o'clock!
(1 row)

Summary

Placing functions in different schemas and setting the correct search_path is enough to overload the way PostgreSQL is searching for a function.

If you still need the original function returning “just” the time, specify the full pathname:

1
2
3
4
5

beer=# SELECT pg_catalog.NOW();
              now
-------------------------------
 2022-05-02 14:13:15.335491+02
(1 row)

This is a good recommendation anyway: if you depend on specific functions or system tables, always prepend the name with the pg_catalog schema name. This way, no one can inject an arbitrary object into your query. The search_path can be modified in different ways you might not be aware of: per role, per database, or globally.

Full example

For reference, here is the full example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

\set ON_ERROR_STOP on
\set ECHO all

SET search_path TO public, pg_catalog;

CREATE OR REPLACE FUNCTION now(beverage TEXT = 'beer')
        RETURNS TEXT
AS $$
    BEGIN

    RETURN 'It''s ' || TRIM(BOTH '''' FROM quote_literal(beverage)) || ' o''clock!';

    END;
$$
LANGUAGE plpgsql
IMMUTABLE
COST 42
PARALLEL SAFE;

SELECT NOW();
SELECT NOW('whisky');