Skip to content

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:

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)

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:

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:

\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');

 

  • Twitter
  • Bookmark It's beer o'clock for PostgreSQL at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg It's beer o'clock for PostgreSQL
  • Bloglines It's beer o'clock for PostgreSQL
  • Technorati It's beer o'clock for PostgreSQL
  • Fark this: It's beer o'clock for PostgreSQL
  • Bookmark It's beer o'clock for PostgreSQL at YahooMyWeb
  • Bookmark It's beer o'clock for PostgreSQL at Furl.net
  • Bookmark It's beer o'clock for PostgreSQL at reddit.com
  • Bookmark It's beer o'clock for PostgreSQL at blinklist.com
  • Bookmark It's beer o'clock for PostgreSQL at Spurl.net
  • Bookmark It's beer o'clock for PostgreSQL at Simpy.com
  • Bookmark It's beer o'clock for PostgreSQL at blogmarks
  • Bookmark It's beer o'clock for PostgreSQL with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

Laurenz Albe on :

A great extension! You could use "LANGUAGE sql", then the function would be simpler and the function call could be inlined for better performance (straight from the tap). In v14, you could also use the new SQL function syntax: "CREATE FUNCTION now(text = 'beer') RETURNS text RETURN format('It''s %s o''clock!', $1);", which is shorter and tracks dependencies (I think tracking alcohol dependencies will be added in v15).
Comments ()

David Fetter on :

I believe the body would have SELECT where you have RETURN, but otherwise be pretty similar. I might have used another layer of dollar quoting, but that's just a coding quirk.
Comments ()

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options