Limit A Table To 1 Row

Поиск
Список
Период
Сортировка
Искать

Limit A Table To 1 Row

От:
<cnliou@eurosport.com>
Дата:
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

Re: Limit A Table To 1 Row

От:
Bruno Wolff III <bruno@wolff.to>
Дата:
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.

Re: Limit A Table To 1 Row

От:
Richard Huxton <dev@archonet.com>
Дата:
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

Re: Limit A Table To 1 Row

От:
"Josh Berkus" <josh@agliodbs.com>
Дата:
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 TEXT NULL
);

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
FAQ