From: | Rob Hoopman <rob(at)tuna(dot)nl> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | RULE with conditional behaviour? |
Date: | 2002-03-09 15:16:44 |
Message-ID: | 3C8A275C.9070701@tuna.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello all,
I've got two versions of the same question;
- The short version:
As I understand it a rule cannot fire a trigger?
- And the long version:
What I am trying to do is this:
I have three tables and a view on those tables ( see end of message if
the view below alone isn't enough info and you're in a helpful mood )
CREATE VIEW field_label_locales AS
-- [REFNAME: flabel_loc]
SELECT
field_labels.id AS field_label_id,
field_labels.label,
fields.id AS field_id,
fields.identifier,
locales.id AS locale_id,
locales.iso639,
locales.iso3166
FROM
field_labels
RIGHT OUTER JOIN
fields ON field_labels.field_id = fields.id
RIGHT OUTER JOIN
locales ON field_labels.locale_id = locales.id;
a select on this view looks like:
field_label_id | label | field_id | identifier | locale_id
| iso639 | iso3166
----------------+----------------+----------+----------------+-----------+--------+---------
1 | Naam Ontvanger | 2 | sender_address | 1
| nl | NL
| | | | 2
| nl | BE
2 | Recipient Name | 2 | sender_address | 3
| en | GB
| | | | 4
| en | US
| | | | 5
| de | DE
What I would like to be able to do is:
UPDATE field_label_locales SET label = 'Sender Name' WHERE locale_id = 1;
So; if field_label_id = NULL, I need to insert a record into
field_labels, else I need to update the record referenced in field_labels.
Can I do this with a rule on the view?
I've created a trigger which works just fine, but I can seem to fire a
trigger with a rule?
I could implement it some other way, but lazy as I am I thought I'd fire
off a mail to the list to see if I am missing something obvious.
Regards,
Rob
====
the relevant bits of the thre tables:
CREATE TABLE locales (
iso639 varchar(2) NOT NULL, -- two character iso639
language code
iso3166 varchar(2), -- two character iso3166
country code
lang_native varchar(80) NOT NULL, -- language name in native
language
country_native varchar(80), -- country name in native language
fallback boolean DEFAULT false, -- If set to true, this locale is
id bigserial,
PRIMARY KEY(id),
UNIQUE(iso639, iso3166)
);
CREATE TABLE fields (
identifier varchar(30),
id bigserial,
PRIMARY KEY(id)
);
CREATE TABLE field_labels (
label varchar(100) NOT NULL,
locale_id bigint NOT NULL,
field_id bigint NOT NULL,
FOREIGN KEY(locale_id) REFERENCES locales ON DELETE RESTRICT ON
UPDATE CASCADE,
FOREIGN KEY(field_id) REFERENCES fields ON DELETE RESTRICT ON UPDATE
CASCADE
);
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-03-09 15:28:23 | Re: How to check for successfull inserts |
Previous Message | Shaun Grannis | 2002-03-09 15:16:04 | Advice for optimizing queries using Large Tables |