[NCLUG] Annoying post-meeting SQL curiosities

[William Steffes]

Aaron Wrote:
> between rows where A is 1 and A is 2.
>
>     CREATE TABLE test (a INTEGER UNIQUE, irrelevantdata VARCHAR(100));
>     -- This index is more-or-less implicit in the UNIQUE column
>     -- constraint above.  Making it explicit.
>     CREATE UNIQUE INDEX test_a_idx ON test (a);
>     -- stuff some sample rows into the table
>     BEGIN;
>     INSERT INTO test (a, irrelevatdata) VALUES
>         (1, 'foo');
>     INSERT INTO test (a, irrelevatdata) VALUES
>         (2, 'bar');
>     INSERT INTO test (a, irrelevatdata) VALUES
>         (3, 'baz');
>     COMMIT;
>     -- stick a new value in here between the after row (1,'foo')
>     -- increasing A in all rows that follow
>     BEGIN;
>     UPDATE test SET a = 0 - a - 1 WHERE a >= 2;
>     INSERT INTO test (a, irrelevantdata) VALUES
>         (2, 'xyzzy');
>     UPDATE test SET a = -a WHERE a < 0;
>     COMMIT;
>
>
>   

I don't know anything about postgresql, but I have used cursors with
other systems.  This may work.

Declare your cursor.   Select from the table in DESC order to make you
get the largest 'a' first.  Loop through the table increment down to and
including a.  Should work in theory.  I do not have postgresql installed
so I cannot test it.

CREATE PROCEDURE mess_with_unique_constraint(new_a INT, new_idata VARCHAR(100))
AS
CURSOR mycursor IS SELECT a FROM test WHERE a >= new_a ORDER BY a DESC;
BEGIN

FOR REC IN mycursor LOOP
UPDATE test SET REC.a = REC.a +1 WHERE a = REC.a;
END LOOP;

INSERT INTO test VALUES (new_a,new_idata);
COMMIT;
END;


I have never used postgresql so your mileage may vary.

William