pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrule or trigger?
3+ messages / 3 participants
[nested] [flat]
* rule or trigger?
@ 2005-06-07 10:28 M.D.G. Lange <mlange@dltmedia.nl>
0 siblings, 2 replies; 3+ messages in thread
From: M.D.G. Lange @ 2005-06-07 10:28 UTC (permalink / raw)
To: pgsql-sql
In order for a "dictionary" system I have created the following system:
tbllanguages
- ID
- name
Primary key ( ID )
tbldictionary
- wordid
- languageid
- value
Primary key ( wordid, languageid)
The idea is to have a word id in several languages, so you only have to
look up the word id and give the language you would like to get the
message in and you'll be presented the translation.
So far so good... only wordid is not unique, making it not suitable to
use it in foreign keys... however I'd like a similar idea:
tblsystemmessages
- ID
- wordid
- pgsqlerrorcode
Primary key ( ID )
"Foreign key" wordid references tbldictionary.wordid
It is not possible to create a constraint Foreign key for "wordid". No
problem there, but I want to be certain that a given wordid exists in
tbldictionary.
Would I have to create a "RULE" or a "TRIGGER" to be certain that the
wordid is existing in tbldictionary in whatever language.
I have the idea that a trigger will not prevent the insertion, or did I
not read well enough?
Would the following rule do what I want?
CREATE OR REPLACE RULE 'systemmessages_rule' AS ON INSERT TO
"public.tblsystemmessages"
DO INSTEAD (
IF count ( SELECT DISTINCT tbldictionary.wordid ) > 0
INSERT INTO tblsystemmessages ( ID, wordid, pgsqlerrorcode )
VALUES ( NEW.ID, NEW.wordid, NEW.pgsqlerrorcode )
ELSE
NOTHING
);
But would this not recursively call this rule?
Michiel
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: rule or trigger?
@ 2005-06-07 13:54 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: M.D.G. Lange <mlange@dltmedia.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2005-06-07 13:54 UTC (permalink / raw)
To: M.D.G. Lange <mlange@dltmedia.nl>; +Cc: pgsql-sql
On Tue, 7 Jun 2005, M.D.G. Lange wrote:
> It is not possible to create a constraint Foreign key for "wordid". No
> problem there, but I want to be certain that a given wordid exists in
> tbldictionary.
> Would I have to create a "RULE" or a "TRIGGER" to be certain that the
> wordid is existing in tbldictionary in whatever language.
>
> I have the idea that a trigger will not prevent the insertion, or did I
> not read well enough?
What you can do in an after trigger is test that the value exists in the
other table or raise an error (which is basically what the foreign key
triggers do).
There are some issues with writing your own, the first being that to get
full foreign key style semantics you need triggers on the referenced table
as well, however if removing or updating the wordid in tbldictionary is
uncommon, you can probably avoid it.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: rule or trigger?
@ 2005-06-07 15:41 Richard Huxton <dev@archonet.com>
parent: M.D.G. Lange <mlange@dltmedia.nl>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2005-06-07 15:41 UTC (permalink / raw)
To: M.D.G. Lange <mlange@dltmedia.nl>; +Cc: pgsql-sql
M.D.G. Lange wrote:
> In order for a "dictionary" system I have created the following system:
>
> tbllanguages
> - ID
> - name
> Primary key ( ID )
>
> tbldictionary
> - wordid
> - languageid
> - value
> Primary key ( wordid, languageid)
>
> The idea is to have a word id in several languages, so you only have to
> look up the word id and give the language you would like to get the
> message in and you'll be presented the translation.
> So far so good... only wordid is not unique, making it not suitable to
> use it in foreign keys... however I'd like a similar idea:
>
> tblsystemmessages
> - ID
> - wordid
> - pgsqlerrorcode
> Primary key ( ID )
> "Foreign key" wordid references tbldictionary.wordid
>
> It is not possible to create a constraint Foreign key for "wordid". No
> problem there, but I want to be certain that a given wordid exists in
> tbldictionary.
I'd split the existence of the word from its presence in the
language-lookup table. Then you can have a reference to the existence of
the "raw word".
raw_words
- wordid
- wordname
Of course "wordname" will probably be in one of your target languages
anyway, but conceptually doesn't have to be.
You can always have triggers on tbldictionary to automatically insert
into raw_words if you'd like.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-06-07 15:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-07 10:28 rule or trigger? M.D.G. Lange <mlange@dltmedia.nl>
2005-06-07 13:54 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-06-07 15:41 ` 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