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 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:
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.
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:
beer=# SELECT NOW(); now ---------------------- It's 'beer' o'clock! (1 row)
And for any other beverage:
beer=# SELECT NOW('whisky'); now ---------------------- It's whisky o'clock! (1 row)
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:
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.
For reference, here is the full example:
\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');