Re: Fwd: conditional rule not applied

Seb wrote:
> Hi,
>
> Apologies for posting this from postgresql.general, but this failed to
> get any follow-ups in that NG. Hopefully someone here can shed some
> light on this.

[snip]

I can give a 'first cut' solution.
But I strongly discourage from doing this in a real world application as
chances are big, that you'll forget something to implement correctly
(nullable fields come to mind immediately as an example).
Your example is also simplified as it makes no sense for instance, that
sh_name is nullable...

here we go:

------------------------------- cut --------------------------------

create or replace function footwear_upd(
in p_sh_id_old integer
,in p_sh_name_old varchar
,in p_sh_avail_old integer
,in p_sl_name_old varchar
,in p_sh_id_new integer
,in p_sh_name_new varchar
,in p_sh_avail_new integer
,in p_sl_name_new varchar
) returns void as $$
declare
l_anz integer := 0;
begin
if p_sh_id_old <> p_sh_id_new then
select count(*) into l_anz from shoelaces sl where sl.sh_id =
p_sh_id_old;
if l_anz > 0 then
raise exception 'Cannot update shoes.sh_id referenced by
shoelace.sh_id';
else
raise notice 'updating sh_id in shoes (but this doesn''t make sense';
update shoes sh set sh.sh_id = p_sh_id_new where sh.sh_id = p_sh_id_old;
end if;
end if;
if p_sh_name_old <> p_sh_name_new then
update shoes sh set sh.sh_name = p_sh_name_new where sh.sh_id =
p_sh_id_old;
end if;
if p_sh_avail_old <> p_sh_avail_new then
update shoes sh set sh.sh_avail = p_sh_avail_new where sh.sh_id =
p_sh_id_old;
end if;
if p_sl_name_old <> p_sl_name_new then
update shoelaces sl set sl_name = p_sl_name_new where sl.sh_id =
p_sh_id_new;
end if;
if p_sl_name_old is null and p_sl_name_new is not null then
insert into shoelaces(sh_id, sl_name) values(p_sh_id_new, p_sl_name_new);
end if;
if p_sl_name_old is not null and p_sl_name_new is null then
delete from shoelaces where sl_name = p_sl_name_old;
end if;
end;
$$ language plpgsql;

CREATE RULE footwear_newshoelaces_upd AS
ON UPDATE TO footwear
do instead
select footwear_upd(old.sh_id, old.sh_name, old.sh_avail, old.sl_name,
new.sh_id, new.sh_name, new.sh_avail, new.sl_name);

------------------------------- cut --------------------------------

this works nicely although the feedback is not really nice:

lem=# SELECT * FROM footwear;
sh_id | sh_name | sh_avail | sl_name
-------+---------+----------+---------
1 | sh1 | 2 | sl1
2 | sh2 | 0 |
3 | sh3 | 4 | sl2
4 | sh4 | 3 |
(4 rows)

lem=# UPDATE footwear SET sl_name = 'sl3' WHERE sh_name = 'sh2';
footwear_upd
--------------

(1 row)

UPDATE 0
lem=# SELECT * FROM footwear;
sh_id | sh_name | sh_avail | sl_name
-------+---------+----------+---------
1 | sh1 | 2 | sl1
2 | sh2 | 0 | sl3
3 | sh3 | 4 | sl2
4 | sh4 | 3 |
(4 rows)

lem=# update footwear set sl_name=null where sh_name='sh2';
footwear_upd
--------------

(1 row)

UPDATE 0
lem=# SELECT * FROM footwear;
sh_id | sh_name | sh_avail | sl_name
-------+---------+----------+---------
1 | sh1 | 2 | sl1
2 | sh2 | 0 |
3 | sh3 | 4 | sl2
4 | sh4 | 3 |
(4 rows)

lem=# UPDATE footwear SET sl_name='sl3' WHERE sh_name='sh2' OR
sh_name='sh4';
footwear_upd
--------------

(2 rows)

UPDATE 0
lem=# SELECT * FROM footwear;
sh_id | sh_name | sh_avail | sl_name
-------+---------+----------+---------
1 | sh1 | 2 | sl1
2 | sh2 | 0 | sl3
3 | sh3 | 4 | sl2
4 | sh4 | 3 | sl3
(4 rows)

lem=#

Cheers, Leo