Smokes your problems, coughs fresh air.

Tag: PostgreSQL (Page 1 of 2)

My profile on PGXN—The PostgreSQL Extension Network—has a list of all my publically released PostgreSQL extensions. Or search my GitHub profile for my PostgreSQL stuff mirrored there.

Semantic version parsing and comparison within PostgreSQL: pg_text_semver

I needed to compare semantic versions within Postgres. I tried the existing semver extension, but when I found that it was a bit problematic to adjust to be fully compliant with the semantic versioning spec, I decided to write a simple domain-based alternative myself: pg_text_semver.

(Version parsing was part of my effort for more robust pg_restore of FlashMQ extension data. I might write more about that in the future.)

pg_extra_time: some data-time functions, operators and casts that I’m surprise are not already part of Postgres

My newest PostgreSQL extension—pg_extra_time—contains the date-time functions, operators and casts that I was surprised to not find in the standard library. Maybe one day…

Again, this extension is a shoot-off from the PostgreSQL work I’m doing for the backend of the high-performant MQTT hosting service, that is due to launch later this quarter.

Like most of the PostgreSQL extensions, pg_extra_time is also available via PGXN—The PostgreSQL Extension Network:

pg_html5_email_address: Postgres email validation in sync. with the HTML5 spec.

Yesterday, I released a tiny PostgreSQL extension that I use to validate email address in the backend of pg_html5_email_address validates email addresses in the same way that modern web browsers do for <input type="email"> elements.

The advantage of having email validation in your data backend to be in sync with the HTML5 spec is twofold:

  1. The HTML5 spec has a more reasonable definition of what constitutes a valid email address than RFC 5322.
  2. It’s convenient to have you HTML5 frontend and PostgreSQL backend follow the same validation rules. Since the backend of our MQTT hosting service is based on PostgREST and the frontend on Vue.js, we find it important that both PostgreSQL and HTML5 + JS have the same ideas about validation.

pg_html5_email_address is available on PGXN:

pg_safer_settings: dealing with PostgreSQL settings more … safely

Yet another PostgreSQL extension that emerged from the FlashMQ PostgreSQL backend is: pg_safer_settings. pg_safer_settings offers some functions and a mechanisms to make working with settings in PostgreSQL a bit safer. Its defining features are:

  1. easy access to database and role-level settings, bypassing session-local and transaction-local settings; and
  2. a type-safe configuration table pattern that allows for IMMUTABLE access to configuration values, and fine-grained access control.

pg_role_fkey_trigger_functions: establishing and maintaining foreign key-like relationships to database ROLEs

Another release from the long list of PostgreSQL extensions that I’m splitting off from the database schemas that make up our rock-solid MQTT hosting platform issss: pg_role_fkey_trigger_functions. That extension name is quite the mouthful. From its readme (again courtesy of pg_readme):

The pg_role_fkey_trigger_functions PostgreSQL extension offers a bunch of trigger functions to help establish and/or maintain referential integrity for columns that reference PostgreSQL ROLE NAMEs.

Many extensions are yet to come, because I’ve found that dependencies in themselves aren’t bad, as long as they are used side by side, and not needlessly stacked on top of each other. That they are no longer 100% internal to the migrations belonging to the app doesn’t worry my, because:

  1. I still maintain them myself anyway, and
  2. the migrations can (and will) use specific extension versions, so that, in fact, no control and cohesion is lost, even while some decoupling and modularity is being won.

pg_rowalesce: COALESCE(), but for rows

Another part of my spin-off PostgreSQL extension series is pg_rowalesce. From its (generated with pg_readme):

The pg_rowalesce PostgreSQL extensions its defining feature is the rowalesce() function. rowalesce() is like coalesce(), but for rows and other composite types. From its arbitrary number of argument rows, for each field/column, rowalesce() takes the value from the first row for which that particular field/column has a not null value.

pg_mockable: inlineable mocking of PostgreSQL functions, like now(), or then()

pg_mockable is one of the spin-off PostgreSQL extensions that I’ve created for the backend of our high-performance MQTT hosting platform: FlashMQ. It’s basic premise is that you (through one of the helper function that the extension provides) create a mockable wrapper function around a function that you want to mock. That wrapper function will be a one-statement SQL function, so that in many cases it will be inlineable.

Then, when during testing you need to mock (for instance now() and its entire family), you can call the mockable.mock() function, which will replace that wrapper function to return the second argument fed to mockable.mock(), until mock.unmock() is called to restore the original wrapper function.

pg_readme: generating a from the COMMENTs in a PostgreSQL extension or schema

I released pg_readme a couple of days ago. From the (auto-generated, inception-style, by pg_readme)

The pg_readme PostgreSQL extension provides functions to generate a document for a database extension or schema, based on COMMENT objects found in the pg_description system catalog.

This is a split-off from the PostgreSQL database structure that I’ve developed for the backend of our managed MQTT hosting platform. There will be many more of split-off PostgreSQL extensions to follow. Although I like my home-grown migration framework (soon to also be released), pushing reusable parts of schemas into separate extensions has a couple of advantages:

  1. easier sharing between projects;
  2. more decoupling between schemas, making migrations easier;
  3. more documentation and testing discipline due to releasing code as open source (peeing in public); and
  4. simpler, cleaner, more well-boundaried code.
« Older posts

© 2023 BigSmoke

Theme by Anders NorenUp ↑