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.
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
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
pg_catalog, and then in
SELECT NOW() will first pick the
now() function which is pre-defined. Let’s change that:
public schema comes before
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.
Since no schema is specified, this function is placed in the first schema in
public. Exactly where I want it.
Let’s call for a beer:
And for any other beverage:
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:
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.
For reference, here is the full example: