Re: Are globally defined constants possible at all ?

Hello, Gregory and Andrew, hello all,

There's been already a lot of discussion around during the Weekend :)

I think both of you are right. However, I didn't mention that we indeed are
using check constraints on the table(s). These, though, can only be used for
checking what goes INTO the tables. Once the values are in (and they are,
now :), I just wanted a decent (= well maintainable, readable and quick) way
to get them selectively back out. So I think the suggestions of Greg are
indeed very valid. For my problem, I'll prefer the third way, i.e. making a
function yielding a fixed ID, not thinking this will give a big function-
calling overhead (although - dear developers ? - a possibility of globally
defined constants would IMHO still be minimally quicker due to not having to
call a function for this).

So thank you all for your nice and helpful reactions,

Regards,

Philippe Bertin

> -----Original Message-----
> From: Gregory Seidman [SMTP:gss+pg(at)cs(dot)brown(dot)edu]
> Sent: vrijdag 7 juni 2002 20:37
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Are globally defined constants possible at all
> ?
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster