Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 864984753A1
	for <pgsql-sql@postgresql.org>; Tue,  6 Aug 2002 23:29:59 -0400 (EDT)
Received: from th00.opsion.fr (th00.opsion.fr [62.39.122.10])
	by postgresql.org (Postfix) with SMTP id 8F514474E44
	for <pgsql-sql@postgresql.org>; Tue,  6 Aug 2002 23:29:58 -0400 (EDT)
Received: from 10.1.1.17 [10.1.1.17] by th00.opsion.fr id 200208070329.32fc;
	Wed, 7 Aug 2002 03:29:50 GMT
Send-By: 202.181.219.129 with Mozilla/4.78 [en] (Win95; U)
To: <pgsql-sql@postgresql.org>
Subject: Limit A Table To 1 Row
From: <cnliou@eurosport.com>
X-Priority: 3 (normal)
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Date: Wed, 7 Aug 2002 03:29:50 GMT
Message-id: <200208070329.32fc@th00.opsion.fr>
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200208/121
X-Sequence-Number: 8967

Hello!

Corporate table, t1, should contain exact 1 row.
I strongly believe that there are cleaner versions
than the following combination of trigger and rule.

Thank you in advance for any improved version!

CNLIOU
--------

CREATE TABLE t1 (CorpId TEXT,CorpName TEXT);

CREATE FUNCTION f1() RETURNS OPAQUE AS '
DECLARE
  rc SMALLINT;
BEGIN
  SELECT COUNT(*) INTO rc FROM t1;

  IF rc > 0 THEN
    RETURN NULL;
  END IF;

  RETURN NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH
ROW EXECUTE PROCEDURE f1();

CREATE RULE rule1 AS ON DELETE TO t1 DO INSTEAD
NOTHING;

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com