pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLimit A Table To 1 Row
4+ messages / 4 participants
[nested] [flat]
* Limit A Table To 1 Row
@ 2002-08-07 03:29 cnliou@eurosport.com
0 siblings, 2 replies; 4+ messages in thread
From: cnliou@eurosport.com @ 2002-08-07 03:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Limit A Table To 1 Row
@ 2002-08-07 03:42 Bruno Wolff III <bruno@wolff.to>
parent: cnliou@eurosport.com
1 sibling, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2002-08-07 03:42 UTC (permalink / raw)
To: cnliou@eurosport.com; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Limit A Table To 1 Row
@ 2002-08-07 03:52 Josh Berkus <josh@agliodbs.com>
parent: cnliou@eurosport.com
1 sibling, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2002-08-07 03:52 UTC (permalink / raw)
To: cnliou@eurosport.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Limit A Table To 1 Row
@ 2002-08-07 08:30 Richard Huxton <dev@archonet.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2002-08-07 08:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-08-07 08:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-07 03:29 Limit A Table To 1 Row cnliou@eurosport.com
2002-08-07 03:42 ` Bruno Wolff III <bruno@wolff.to>
2002-08-07 03:52 ` Josh Berkus <josh@agliodbs.com>
2002-08-07 08:30 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox