--
-- 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;