Thu Jun 22 15:11:38 2017
Here's a small function to create an assertion, which throws an error if the first argument is not true. Public domain.
create or replace function assert(pass boolean, msg text default null)
returns void
language plpgsql
as $$
begin
if pass then
return;
end if;
if msg is null then
get diagnostics msg = PG_CONTEXT;
end if;
raise exception '%', msg;
end;
$$;
Posted in / software / postgres
Wed Mar 15 17:09:03 2017
I frequently need to get the definition of a function out of postgres, so here's a small shell script I wrote to do that.
#!/bin/sh
{
cat <<EOS
select pg_get_functiondef(oid)
from pg_proc where proname = '$1'
;
EOS
} | psql -Aqt
Posted in / software / postgres
Tue Jul 7 05:19:50 2015
I am working on a basic ticketing system, and in the ticket table I have an "Impact" column, which should only allow a particular set of pre-defined impacts. The problem with using a numeric value for "severity" or similar is that people don't know what the numbers are supposed to mean and they tend to overstate the severity of their particular ticket.
So I have, rather than "severity", "impact". The values I have tentatively decided on are
These aren't necessarily the best names, "Inconvenient" should perhaps be separated into "Has Work Around", and "Partial Functionality" or something like that.
We would however like to sort by the impact. There are a couple of ways to do this naively.
You could cleverly choose the names of the field to be in alphabetical order. I mention this only because it occurred to me. There are probably enough synonyms that you could always make this work, but the names would more likely than not become quite tortured.
The other way is to have a separate "impact type" table with the name and a sort order or numerical level. But you'd have to join this table every time you wanted to sort or every time you wanted to get the name for the level depending on whether you chose the name or the number to be the primary key.
Neither of these solutions is very elegant. So, enter the enum.
We can create an enum thus:
create type impact as enum (
'Informational',
'Inconvenient',
'Unusable',
'Production Outage'
);
Which allows us to sort, compare, and generally use the labels instead of numbers. Internally these are stored in four bytes and postgres handles the conversions for you. They're effectively a 32 bit float as far as the storage is concerned.
So far so good, but I'm writing an external application which needs to know what order the enum labels are in. Fortunately, it is simple to query the catalogs and get this information. I wrote a helper function:
create function enumlist(enumname text) returns
setof pg_enum language 'sql' as $$
select * from pg_enum where enumtypid = enumname::regtype::oid
$$ stable strict;
Which can be used to get the labels and sort order for them for use by an external application.
% psql -c "select * from enumlist('impact')"
enumtypid | enumsortorder | enumlabel
-----------+---------------+-------------------
19525184 | 1 | Informational
19525184 | 2 | Inconvenient
19525184 | 3 | Unusable
19525184 | 4 | Production Outage
(4 rows)
%
The advantage of this is that you don't have to hard-code the values anywhere, and the database is the master repository of your label definitions. If your applications query this as needed, any change in the database will be automatically reflected in your application logic and displays. Any time you can avoid a double edit, that's a win for maintainability.
Posted in / software / postgres