On Thu, 10 May 2001, David Wheeler wrote:
> Hi All,
>
> I need to create a custom constraint (or a trigger?) on a table, and could
> use some help.
<snip />
To answer my own question, this is what I've come up with. To anyone who
happens to decide to entertain him/herself by looking this over: if you
happen to see any inefficiencies in how I'm doing this, and can tell me
how it might be done better, I would appreciate the comments! Here's my
solution:
--
-- TABLE: usr
--
CREATE TABLE usr(
login VARCHAR(128) NOT NULL,
active NUMERIC(1, 0) NOT NULL
CONSTRAINT ck_usr__active CHECK (active IN (1,0))
DEFAULT 1
);
--
-- FUNCTION: login_avil
--
-- This function is used by the table constraint ck_usr__login below to
-- determine whether the login can be used. The rule is that there can be any
-- number of rows with the same login, but only one of them can be active. This
-- allows for the same login name to be recycled for new users, but only one
-- active user can use it at a time.
CREATE FUNCTION login_avail(varchar, int4) RETURNS BOOLEAN
AS 'SELECT CASE WHEN
(SELECT 1
FROM usr
WHERE $2 = 1
AND login = $1
AND active = 1) > 0
THEN false ELSE true END'
LANGUAGE 'sql'
WITH (isstrict);
-- Now apply the constraint to the login column of the usr table.
ALTER TABLE usr ADD CONSTRAINT ck_usr__login CHECK (login_avail(login, active));
Thanks!
David
--
David Wheeler AIM: dwTheory
[EMAIL PROTECTED] ICQ: 15726394
Yahoo!: dew7e
Jabber: [EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
