-- -- workflow__move_role_up/2 -- create or replace function workflow__move_role_up( character varying, character varying ) returns int4 as $$ declare move_role_up__workflow_key alias for $1; move_role_up__role_key alias for $2; v_this_sort_order integer; v_prior_sort_order integer; begin select sort_order into v_this_sort_order from wf_roles where workflow_key = move_role_up__workflow_key and role_key = move_role_up__role_key; select max(sort_order) into v_prior_sort_order from wf_roles where workflow_key = move_role_up__workflow_key and sort_order < v_this_sort_order; if not found then /* already at top of sort order */ return; end if; /* switch the sort orders around */ update wf_roles set sort_order = (case when role_key=move_role_up__role_key then v_prior_sort_order else v_this_sort_order end) where workflow_key = move_role_up__workflow_key and sort_order in (v_this_sort_order, v_prior_sort_order); return 0; end;$$ language plpgsql;