Hello,
I'm considering creating a TYPE for what may be called a "possibly
imprecise date" (pidate). The most obvious use is for recording dates
such as births or deaths of historical individuals, where we may know
that someone died precisely on a given year-month-day, but the birth may
only be known down to year-month or just the year (or perhaps we know
precisely the baptism date [Adam Smith], but not the actual birth, so we
want to record the former but qualified so it can be annotated on
display). Another use is for publications, like magazines that are
issued on a monthly basis or journals that are issued on a quarterly or
seasonal basis.
We currently have two instances of this kind, using a standard DATE
column plus a CHAR(1) column that encodes (on a limited basis for now)
the YMD, YM or Y level of precision, and a simple SQL function to return
a textual representation of the pidate. It would be nice to generalize
this before going further.
The first option I explored was creating a composite type with the two
attributes, but that doesn't allow specification of DEFAULTs, NOT NULL
or CHECK expressions on the precision code attribute. It seems I'd have
to create a DOMAIN first, then use DATE and that domain to create a
composite TYPE, to finally use the latter in actual tables. That
layering looks cumbersome.
Another option, which I havent't tried, is to subvert PG by creating an
empty table, since that creates a "record type", but even if possible
that would be a hack.
Finally there's the base TYPE. This entails writing some seven
functions "in C or another low-level language" (does PG support *any*
other such language?), plus installing a library with those functions in
a production environment. Doable, yes, but not very friendly either.
Am I overlooking something or is the practice of creating abstractions
in object-relational databases mostly unchanged?
Regards,
Joe