From: | David Wheeler <David(at)Wheeler(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Custom Constraint |
Date: | 2001-05-10 20:58:30 |
Message-ID: | Pine.LNX.4.21.0105101355450.4415-100000@theory.photodb.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
David(at)Wheeler(dot)net ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-05-10 21:04:49 | Re: NAMEDATALEN |
Previous Message | Mikheev, Vadim | 2001-05-10 20:57:40 | RE: COPY locking |