RULE with conditional behaviour?

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
);

Responses

Browse pgsql-general by date

  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