I need to produce a stored procedure in Ingres capable of taking a list of IDs and producing a new set of IDs which are unrelated to the first set (but where we store each pair).
My initial plan was to sort by random(), and use a sequence to simply number each row from 100000, then save these results into a table. This mostly works. However, because ALTER SEQUENCE cannot be used inside a stored procedure I have no way of ensuring that the new ids always start from 100000.
CREATE PROCEDURE create_external_dataset (datasetid varchar(12) NOT NULL) AS
BEGIN
INSERT INTO external_sids
SELECT :datasetid, NEXT VALUE FOR random_order_rank, sid
FROM id_table
ORDER BY random()
FETCH FIRST 300000 ROWS ONLY;
END;
ALTER SEQUENCE random_order_rank RESTART WITH 100000;
EXECUTE PROCEDURE create_external_dataset('LTF-5463');
Is there any way of resetting a sequence from within a stored procedure or creating a stored procedure only instance of a sequence?
Failing that, is there a way of producing incremental numbers in Ingres which does not use sequences?
If you are using ingres 10.2 there is a function called ROW_NUMBER(), which could be substituted for a sequence in this instance.
ROW_NUMBER() Returns the ordinal position of each result row within a partition, based on the sequence defined by the ordering definition for the window. Rows with the same values in their sort specification are ordered arbitrarily. For example: 1, 2, 3, 4, 5. The following query assigns a consecutive number to each row. Rows with matching numbers are ordered arbitrarily.
SELECT empno, deptno, sal,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) AS rownum
FROM emp;
EMPNO DEPTNO SAL ROWNUM
---------- ---------- ---------- ----------
934 100 1200 1
782 100 2350 2
839 100 4900 3
369 200 700 1
876 200 1000 2
566 200 2875 3
788 200 2900 4
902 200 2900 5
900 300 850 1
654 300 1150 2
521 300 1150 3
844 300 1400 4
499 300 1500 5
698 300 2750 6
In earlier versions of Ingres there is no direct solution for this problem. Options to consider are:
If the number of rows is constant and known, you can create a repeating sequence, removing the need to reset the sequence (it really does need to be constant).
CREATE SEQUENCE random_order_rank AS INTEGER START WITH 10000 INCREMENT BY 1 MINVALUE 10000 MAXVALUE 60000 CYCLE;
If the rows are not constant, you could set the sequence to repeat over a length larger than your maximum possible rows, write this to a table, and then perform an update which subtracts a value to bring your ids back in range. If doing this you would also need to detect when the sequence had looped and deal with the large and small ids independently.