--- In firebird-support@yahoogroups.com, Markus Ostenried <macnoz@...> wrote:

>
> On Sat, Mar 14, 2009 at 18:23, woodsmailbox <cosmin.apreutesei@...> wrote:
> >> > I know deferred constraints would solve it, but is there any other way in the absence of it? Assigning an out-of-domain value is not acceptable, since it breaks the domain definition. Thanks.
> >>
> >> Maybe you could swap (update) values of all other columns and leave the
> >> unique column as it is?
> >
> > Twisted :) But I can't, since one of the other columns will always have a PK constraint on it.
> >
> > For example, the ORDER_INDEX column is used for custom ordering of values. I want to be able to move a row's position in the dataset updating that column.
>
> I hope I'm not missing the obvious here, but can't you assign a
> temporary value like so:
>
> procedure swap (pkvalue1 integer, pkvalue2 integer)
> as
> variable oderindex1 integer;
> variable oderindex2 integer;
> begin
> -- read original values
> select order_index from tbl where pk = :pkvalue1 into :oderindex1;
> select order_index from tbl where pk = :pkvalue2 into :oderindex2;
> -- temporarily assign a magic value to one of the records
> update tbl set order_index = MAX_INT where pk = :pkvalue2;
> -- update both records with swapped values
> update tbl set order_index = :oderindex2 where pk = :pkvalue1;
> update tbl set order_index = :oderindex1 where pk = :pkvalue2;
> end
>
> HTH,
> Markus
>

It's what I do currently, but I need this in too many places, and since PSQL and SQL itself (not firebird's in particular) sucks at abstracting computation of any kind (read: has absolutely no means to do that), I have to abuse copy/paste to implement this solution.