On Sun, Jan 22, 2006 at 02:25:33PM -0500, Tom Lane wrote:
> I seem to recall that someone had come up with a datatype that would
> store numbers with units attached, which seems like what you want here.
> Check the PG list archives, and/or poke around on pgfoundry and gborg.
Hmm, I only just noticed this thread, but it might work for him. OTOH,
if feet are the only units he's interested in then it's slight
overkill. Also, it doesn't deal with alternate spellings (1 foot,2
feet). If you decided you would only use abbreviations that wouldn't
matter ofcourse.
http://svana.org/kleptog/pgsql/taggedtypes.html
Quick example:
test=# create table physics_units ( name text, abbrev text );
CREATE TABLE
test=# insert into physics_units values ('feet','ft');
INSERT 2205045 1
test=# insert into physics_units values ('metres','m');
INSERT 2205046 1
test=# select create_tagged_type( 'physics_type', 'float', 'physics_units' );
NOTICE: type "physics_type" is not yet defined
DETAIL: Creating a shell type definition.
NOTICE: argument type physics_type is only a shell
create_tagged_type
--------------------
(1 row)
test=# select create_tagged_operator('physics_type','+','physics_type','physics_type');
NOTICE: +(physics_type,physics_type) => physics_type maps to +(double precision,double precision) => double precision
create_tagged_operator
------------------------
(1 row)
test=# select create_tagged_operator('physics_type','*','float','physics_type');
NOTICE: *(physics_type,double precision) => physics_type maps to *(double precision,double precision) => double
precision
create_tagged_operator
------------------------
(1 row)
test=# select '10 feet'::physics_type + '22 feet'::physics_type;
?column?
----------
32 feet
(1 row)
test=# select '10 feet'::physics_type * 5;
?column?
----------
50 feet
(1 row)
test=# select '10 feet'::physics_type + '3 metres'::physics_type;
ERROR: Using tagged operator +(physics_type,physics_type) with incompatable tags (feet,metres)
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.