Re: Are globally defined constants possible at all ? - Mailing list pgsql-general

Bertin, Philippe sez:
} Hello, all,
}
} In a database we are developing, we use keys in several places. For several
} reasons (a.o. speed), these have a type of integer. Select- statements
} selecting on such a key (e.g. KindID) have a clause like "... where KindID =
} 3". In this case, the integer value 3 stands for "software". This is not so
} very well readable, to my feeling.

I am in the exact same position. I have a number of things which are
essentially enumerations. Since PostgreSQL does not support an enumeration
type (MySQL does, but then it doesn't have a proper boolean type), I have a
whole lot of small tables that are the mapping of number to string value.
This has the added benefit that the columns for these types REFERENCE the
enumeration tables, enforcing the enumeration constraint (i.e. the column
can only take on values that appear in the enumeration table). Importantly,
both columns are indexed (one because it's a primary key, the other because
it's UNIQUE):

CREATE TABLE Type_enum (
    id int,
    value text UNIQUE,
    primary key (id)
);

} Now my question : is there a decent way (e.g. *globally* defined constants,
} or defines, or something else) by which we could make the above mentioned
} clause sound something like "... where KindID = SOFTWARE". I've read a fair
} part of the PostgreSQL documentation now, but haven't seen anything like
} this exists (I'm not a 15- year experienced DBA, you see).

There are three ways to actually accomplish this. The first two use the
table I mentioned about. The third does not require them, though you may
want the integrity constraints anyway.

1. use the enumeration table in a join (this is what I do, though I'm still
   designing and may change my mind)

... where TBL.KindID = ENUM.id AND ENUM.value = 'SOFTWARE' ...

2. create a function and use it in your queries

CREATE FUNCTION EnumType(text) RETURNS int AS 'SELECT id FROM Type_enum
WHERE value = $1' LANGUAGE SQL with (isstrict);

... where KindID = EnumType('SOFTWARE') ...

3. create a function for each type and use it in your queries

CREATE FUNCTION EnumTypeSoftware() RETURNS int AS 'SELECT 1' LANGUAGE SQL
with (isstrict);

CREATE FUNCTION EnumTypeHardware() RETURNS int AS 'SELECT 2' LANGUAGE SQL
with (isstrict);

... where KindID = EnumTypeSoftware() ...

} My intention is not to change the type of the keys (in this case e.g.
} KindID), nor to redefine a constant in every *separate* function or
} procedure. Any ideas on how to tackle this problem elegantly ?

This is my solution. YMMV. I would welcome any comments on how good a
solution this is. I have not yet deployed it, so a compelling reason to
change my approach would be useful.

} TIA,
}     Philippe Bertin
--Greg