From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Peter Eisentraut <peter_e(at)gmx(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Paesold <mpaesold(at)gmx(dot)at>, PostgreSQL Hackers Mailing List <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Bug in PL/pgSQL GET DIAGNOSTICS? |
Date: | 2002-09-29 19:26:37 |
Message-ID: | mfiepukfjntpudu7jpqkihovgiqravb37k@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sat, 28 Sep 2002 19:20:43 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)us> wrote:
>OK, that is a good example. It would return the sum of the matching
>tags. You are suggesting here that it would be better to take the
>result of the last matching tag command, right?
The examples were meant to support my previous suggestion of
explicitly marking the statement you want to be counted, something
like:
CREATE VIEW twotables AS
SELECT ... FROM table1 INNER JOIN table2 ON ... ;
CREATE RULE twotables_insert AS -- INSERT rule
ON INSERT TO twotables
DO INSTEAD (
COUNT INSERT INTO table1 VALUES (new.pk, new.col1);
INSERT INTO table2 VALUES (new.pk, new.col2)
);
CREATE RULE twotables_update AS -- UPDATE rule
ON UPDATE TO twotables
DO INSTEAD (
COUNT UPDATE table1 SET col1 = new.col1 WHERE pk = old.pk;
UPDATE table2 SET col2 = new.col2 WHERE pk = old.pk
);
CREATE RULE twotables_delete AS -- DELETE rule
ON DELETE TO twotables
DO INSTEAD (
COUNT DELETE FROM table1 WHERE pk = old.pk;
DELETE FROM table2 WHERE pk = old.pk
);
CREATE VIEW visible AS
SELECT ... FROM table3
WHERE deleted = 0;
CREATE RULE visible_delete AS -- DELETE rule
ON DELETE TO visible
DO INSTEAD
COUNT UPDATE table3
SET deleted = 1
WHERE pk = old.pk;
One argument against automatically "don't count non-INSTEAD rules and
count the last statement in INSTEAD rules": sql-createrule.html says:
| for view updates: there must be an unconditional INSTEAD rule [...]
| If you want to handle all the useful cases in conditional rules, you
| can; just add an unconditional DO INSTEAD NOTHING rule [...]
| Then make the conditional rules non-INSTEAD
CREATE RULE v_update AS -- UPDATE rule
ON UPDATE TO v
DO INSTEAD NOTHING;
CREATE RULE v_update2 AS -- UPDATE rule
ON UPDATE TO v WHERE <condition1>
DO (
COUNT ...
);
CREATE RULE v_update3 AS -- UPDATE rule
ON UPDATE TO v WHERE <condition2>
DO (
COUNT ...
);
Servus
Manfred
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2002-09-29 19:43:45 | Re: [SQL] CURRENT_TIMESTAMP |
Previous Message | Dave Page | 2002-09-29 19:11:53 | psqlODBC *nix Makefile (new 7.3 open item?) |