Re: Fwd: conditional rule not applied - Mailing list pgsql-sql

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,inp_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
shset 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
updateshoes 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
shoelaceswhere 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