| 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: | Whole Thread | Raw Message | 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 |