Обсуждение: Are globally defined constants possible at all ?
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. 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). 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 ? TIA, Philippe Bertin Software Development Engineer Avionics
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
On Fri, Jun 07, 2002 at 02:36:35PM -0400, Gregory Seidman wrote: > 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 Why don't you use a CHECK contraint? That's the standard way to do it. A -- ---- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M6K 3E3 +1 416 646 3304 x110
Andrew Sullivan sez: } On Fri, Jun 07, 2002 at 02:36:35PM -0400, Gregory Seidman wrote: } } > 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 } } Why don't you use a CHECK contraint? That's the standard way to do } it. Er, could you expand on that? Are you suggesting something like this: CREATE TABLE Foo ( KindID int, ... CONSTRAINT fooenum CHECK KindID IN ('SOFTWARE', 'HARDWARE') ); } A --Greg
On Fri, 7 Jun 2002, Andrew Sullivan wrote: > On Fri, Jun 07, 2002 at 02:36:35PM -0400, Gregory Seidman wrote: > > > 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 > > Why don't you use a CHECK contraint? That's the standard way to do > it. I wonder if you could explain how to use CHECK for this please? I'm having a little trouble thinking in that manner but then I've only seen and used the table associating name and value method before and so am a little biased. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
Check can be used in the following way status VARCHAR(32) DEFAULT 'new' NOT NULL CHECK(status IN ('new','read','deleted')), HTH Darren Ferguson On Fri, 7 Jun 2002, Nigel J. Andrews wrote: > > On Fri, 7 Jun 2002, Andrew Sullivan wrote: > > > On Fri, Jun 07, 2002 at 02:36:35PM -0400, Gregory Seidman wrote: > > > > > 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 > > > > Why don't you use a CHECK contraint? That's the standard way to do > > it. > > I wonder if you could explain how to use CHECK for this please? I'm having a > little trouble thinking in that manner but then I've only seen and used the > table associating name and value method before and so am a little biased. > > > -- > Nigel J. Andrews > Director > > --- > Logictree Systems Limited > Computer Consultants > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On Fri, Jun 07, 2002 at 05:29:27PM -0400, Gregory Seidman wrote: > > Er, could you expand on that? Are you suggesting something like this: > > CREATE TABLE Foo ( > KindID int, > ... > CONSTRAINT fooenum > CHECK KindID IN ('SOFTWARE', 'HARDWARE') > ); Yes. Except I think you need another set of parentheses check("KindID" IN ('SOFTWARE','HARDWARE')) A -- ---- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M6K 3E3 +1 416 646 3304 x110
On Fri, 7 Jun 2002, Andrew Sullivan wrote: > On Fri, Jun 07, 2002 at 05:29:27PM -0400, Gregory Seidman wrote: > > > > Er, could you expand on that? Are you suggesting something like this: > > > > CREATE TABLE Foo ( > > KindID int, > > ... > > CONSTRAINT fooenum > > CHECK KindID IN ('SOFTWARE', 'HARDWARE') > > ); > > Yes. Except I think you need another set of parentheses > > check("KindID" IN ('SOFTWARE','HARDWARE')) Can this be done? What are the integers derived from the strings and is there any chance of them not being unique? The following message shows a what I think is a more consistent way of writing it but I still can't see how use of CHECK makes the mapping from a textual type to integer. Darren Ferguson wrote on Sun Jun 9 at 16:38:53 2002 > >Check can be used in the following way > >status VARCHAR(32) DEFAULT 'new' NOT NULL > CHECK(status IN ('new','read','deleted')), > >On Fri, 7 Jun 2002, Nigel J. Andrews wrote: > > >> On Fri, 7 Jun 2002, Andrew Sullivan wrote: >> > >> > On Fri, Jun 07, 2002 at 02:36:35PM -0400, Gregory Seidman wrote: >> > >> > > 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 >> > >> > Why don't you use a CHECK contraint? That's the standard way to do >> > it. >> >> I wonder if you could explain how to use CHECK for this please? I'm having a >> little trouble thinking in that manner but then I've only seen and used the >> table associating name and value method before and so am a little biased. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes: > ... I still can't see how use of CHECK makes the mapping from a textual type > to integer. It doesn't. The point people are making is that storing a (short) text string is a perfectly respectable way to do this. If you're feeling a compulsion to micro-optimize, though, I'd recommend the trick I used to use: use datatype "char" (note the quotes). This is *one* byte on disk, even smaller than integer, and you can still choose characters with some mnemonic value for your different states. This method works pretty well up to a dozen or two possible states, after which it starts to get hard to choose values; but beyond that point I'd think a separate table is the way to go anyway. regards, tom lane
The only way you can do this is basically say 0 is SOFTWARE and 1 is HARDWARE. This can just be inferred or you could have a dictionary table seperate that has CREATE dictionary_table ( KindID integer not null default nexval('sequence_name'), name varchar(32) not null, .... CONSTRAINT dt_pk PRIMARY KEY(KindID), CONSTRAINT dt_un UNIQUE(name) ); The choice is up to yourself and you can make KindID in foo reference the dictionary table HTH Darren Ferguson On Sun, 9 Jun 2002, Nigel J. Andrews wrote: > > On Fri, 7 Jun 2002, Andrew Sullivan wrote: > > > On Fri, Jun 07, 2002 at 05:29:27PM -0400, Gregory Seidman wrote: > > > > > > Er, could you expand on that? Are you suggesting something like this: > > > > > > CREATE TABLE Foo ( > > > KindID int, > > > ... > > > CONSTRAINT fooenum > > > CHECK KindID IN ('SOFTWARE', 'HARDWARE') > > > ); > > > > Yes. Except I think you need another set of parentheses > > > > check("KindID" IN ('SOFTWARE','HARDWARE')) > > Can this be done? What are the integers derived from the strings and is there > any chance of them not being unique? > > The following message shows a what I think is a more consistent way of writing > it but I still can't see how use of CHECK makes the mapping from a textual type > to integer. > > > Darren Ferguson wrote on Sun Jun 9 at 16:38:53 2002 > > > >Check can be used in the following way > > > >status VARCHAR(32) DEFAULT 'new' NOT NULL > > CHECK(status IN ('new','read','deleted')), > > > >On Fri, 7 Jun 2002, Nigel J. Andrews wrote: > > > > > >> On Fri, 7 Jun 2002, Andrew Sullivan wrote: > >> > > >> > On Fri, Jun 07, 2002 at 02:36:35PM -0400, Gregory Seidman wrote: > >> > > >> > > 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 > >> > > >> > Why don't you use a CHECK contraint? That's the standard way to do > >> > it. > >> > >> I wonder if you could explain how to use CHECK for this please? I'm having a > >> little trouble thinking in that manner but then I've only seen and used the > >> table associating name and value method before and so am a little biased. > > > -- > Nigel J. Andrews > Director > > --- > Logictree Systems Limited > Computer Consultants > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
The only thing that I'd like to add are the following 3 points: 1st POINT: I wished for global constants many times. 2nd POINT: I even have good use for a table constant, or virtual column. E.g., a table that I would create like this: CREATE TABLE Foo( aVar INTEGER, aConst INTEGER CONSTANT='15' ); I know, this could be done with a VIEW, but I would like to use it as follows: ALTER TABLE Foo ADD FOREIGN KEY(aVar, aConst) REFERENCES Bar(val1, val2); see what I mean? The same could be done without the aConst column if this were allowed: ALTER TABLE Foo ADD FOREIGN KEY(aVar, '15') REFERENCES Bar(val1, val2); It's too bad that not at least the contant expression in the foreign key reference is allowed. Until that is the case I need to redundantly store the aConst in every tuple of the Foo table (multiply this by 600 million and you begin to see my point :-). 3rd POINT: For global constants a constant function is a very nice way of doing it, look again at Gregories 3rd suggestion: Gregory Seidman wrote: > 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() ... This is so nice because (a) it can be done now. And (b) a named variable is theoretically precisely this, a function without arguments. thanks, -Gunther -- Gunther Schadow, M.D., Ph.D. gschadow@regenstrief.org Medical Information Scientist Regenstrief Institute for Health Care Adjunct Assistant Professor Indiana University School of Medicine tel:1(317)630-7960 http://aurora.regenstrief.org
> 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); As a side note I didn't see mentioned, you might want to define those as iscachable (and I'm not sure isstrict buys you anything for a function with no args) to allow index scans to be used when you do col=func() where clauses.
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@cs.brown.edu] > Sent: vrijdag 7 juni 2002 20:37 > To: pgsql-general@postgresql.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