I’ll be the first to admit that I found Postgres window functions fantastically confusing when I first encountered them. They’re a powerful and versatile tool for building reports and summaries, but that functionality hides behind a fairly steep learning curve. One of the ways to combat their inherent complexity is to fully explore how they work, instead of just trying to wrap our heads around the expected results.
Window doggies have gotten decidedly smug
There are a lot of languages available for authoring Postgres functions, but there’s nothing quite like the the classic PL/pgSQL. It’s SQL! It’s not SQL! It’s a kind of horrifying mutant crossbreed suitable only for terrifying small children and generating complex reports from a cavalcade of dubious sources! And deep within its twisted entrails is an often overlooked feature usually only available in far more mature entities.
That’s right, it’s obvious we’re referring to the ASSERT statement.
Connecting to a Postgres database can be a headache for end users and DBAs alike. Not because of the work involved, but the general irritation of managing passwords—an issue common to any system that requires authentication controls.
The user wants to say, “Who am I? None of your business!”
While the database is more comfortable with, “Go away, before I taunt you a second time!”
Well, there’s some middle ground everyone can enjoy, and a few Postgres-specific caveats which add a bit of flavor to the whole interaction.
A few days ago, a developer came to me with that inevitable scenario that every DBA secretly dreads: a need for a dynamic table structure. After I’d finished dying inside, I explained the various architectures that could give him what he needed, and then I excused myself to another room so I could weep silently without disturbing my coworkers. But was it really that bad? Databases have come a long way since the Bad Old Days when there were really only two viable approaches to table polymorphism.
In a heterogeneous database environment, it’s not uncommon for object creation and modification to occur haphazardly. Unless permissions are locked down to prevent it, users and applications will create tables, modify views, or otherwise invoke DDL without the DBA’s knowledge. Or perhaps permissions are exceptionally draconian, yet they’ve been circumvented or a superuser account has gone rogue. Maybe we just need to audit database modifications to fulfill oversight obligations. Whatever the reason, Postgres has it covered with event triggers.
