Обсуждение: Limit A Table To 1 Row

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

On Wed, Aug 07, 2002 at 03:29:50 +0000, cnliou@eurosport.com wrote:
> 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!

Another way to keep someone from inserting a second row is to have a primary
key and a constraint fixing the primary key to a fixed value.

Cnliou,

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

Actually, your method looks quite effective and relatively simple.

However, you may want to reconsider this method of storing values in
the database.  What I do is create a table called global_admin:

CREATE TABLE global_admin (value_name VARCHAR(30) NOT NULL PRIMARY KEY,value_format VARCHAR(30) NOT NULL,admin_value
TEXTNULL
 
);

And then I can store all of my random, persistent setting data in one
table:

value_name    value_format    admin_value
'Coporation'    'Text'        'Lugini Corp., Inc.'
'Home Town'    'Text'        'Singapore'
'Timeout Interval'    'Interval'        '30 minutes'
'Max Reports'    'Integer'        '5'
etc.

-Josh Berkus

On Wednesday 07 Aug 2002 4:52 am, Josh Berkus wrote:

> However, you may want to reconsider this method of storing values in
> the database.  What I do is create a table called global_admin:
>
> CREATE TABLE global_admin (
>     value_name VARCHAR(30) NOT NULL PRIMARY KEY,
>     value_format VARCHAR(30) NOT NULL,
>     admin_value TEXT NULL
> );

I do as Josh does (which has got to put some doubts in Josh's mind) except I
add value_section and value_comment fields to make it easier for someone to
edit where necessary.

- Richard Huxton