Thread: 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