Creating and Calling a PL/SQL Function that sets the currval of a sequence

I came across this problem recently. I needed to update the current value of a sequence in existing databases. This is due to an erroneous current value set in the initial install.

My requirements were:
1. The patch should be able to detect whether or the current value of the sequence is the erroneous value. If it is, it should be updated to the new value.
2. Additions to the DB that incremented the current value should not be overriden with the new value.

Using the following function, this can be accomplished:

CREATE OR REPLACE FUNCTION schema_name.checkSeq() RETURNS integer AS
$BODY$
declare
v_CurrVal int4;
begin
select nextval('schema_name.sequence_name') into v_CurrVal;

IF v_CurrVal < 10 then
PERFORM setval('schema_name.sequence_name', 32, true);
v_CurrVal = 32;
RETURN v_CurrVal;
END IF;

PERFORM setval('redflex_express.seq_incidentproperties_id', v_CurrVal - 1, true);
v_CurrVal = v_CurrVal - 1;
RETURN v_CurrVal;

RETURN currval('redflex_express.seq_incidentproperties_id');
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;


This checks to gets the nextVal of the schema_name.sequence_name sequence and sets it to 32 if it is less than 10. Using currVal did not yield the required results. Hence, the nextVal is used and decremented by 1.

To call this function, use this:

select * from schema_name.checkSeq();