Forum OpenACS Development: Re: fix for Ad Server package

Collapse
Posted by Malte Sussdorff on
Okay, I take it back, I got it to work, typo on my side, silly me.

The patch is applied, an upgrade script is written, version number is up, maturity level is reduced (due to changes only in postgresql and not in oracle) and I don't release it unless someone can check and verify it works on an upgrade.

Collapse
Posted by Malte Sussdorff on
I can't resolve the bug #1916. Maybe someone else has a clue why.
Collapse
Posted by Orzenil Silva Junior on
Thank you Malte for the patch applied.

Upgrade script does not work for me. The error was:

psql:upgrade-4.2-4.3d1.sql:36: ERROR: ProcedureCreate: cannot change return type of existing function.
Use DROP FUNCTION first.

I used the following sript for postgresql update:

#upgrade-4.2-4.3d1.sql
-- statement level trigger to perform the swaps.
drop function advs_count_afr_del_fun() CASCADE;

create function advs_count_afr_del_fun() returns opaque as '
declare
nextval integer;
s record;
begin
-- find the highest numbered ad
-- advs_properties is guaranteed to exist.
select adv_count
into nextval
from advs_properties;
-- for update; -- do I need the for update?

-- for each adnumber to be swapped do
for s in select swap from advs_swaps order by swap desc loop

-- find the ad that has that number and renumber it
update advs
set adv_number = s.swap
where adv_number = nextval - 1;

-- delete the row
delete
from advs_swaps
where swap = s.swap;

nextval := nextval - 1;
end loop;

-- update the highest number
update advs_properties
set adv_count = nextval;

return new;
end;
' language 'plpgsql';

create trigger advs_count_afr_del
after delete on advs for each row execute procedure advs_count_afr_del_fun();