PostgreSQL–Generate an auto incrementing number on year basis

For an application we are building we had the following requirement:

A unique increasing number should be generated for every document on a year by year basis. So at the end of each year the document counter should be reset to 0 and start to increase again.

As we are using PostgreSQL as our database, I decided to implement this feature using sequences.

First thing I did was creating a function that generates a new sequence based on a specific name:

	CREATE OR REPLACE FUNCTION public.f_create_seq(_seq text)
	RETURNS void AS
	$func$
	DECLARE
	_fullname text := format('%I.%I', current_schema,_seq);
	_relkind "char" := (SELECT c.relkind
	FROM pg_namespace n
	JOIN pg_class c ON c.relnamespace = n.oid
	WHERE n.nspname = current_schema
	AND c.relname = _seq);
	BEGIN
	IF _relkind IS NULL THEN -- name is free
	EXECUTE 'CREATE SEQUENCE ' || _fullname || 'START 1';
	RAISE NOTICE 'New sequence % created.', _fullname;
	END IF;
	END
	$func$ LANGUAGE plpgsql;

view raw createsequence.sql hosted with ❤ by GitHub

This allows me to create a new sequence dynamically at the beginning of each year.

Next thing I did was creating another function that will first invoke the f_create_seq function to see if a sequence already exists and then calls this sequence to get the next number in line.

	CREATE OR REPLACE FUNCTION public.f_next_seq(_seq text)
	RETURNS integer AS
	$func$
	DECLARE sequence INTEGER;
	BEGIN
	PERFORM public.f_create_seq(_seq);
	sequence := (SELECT nextval(_seq));
	RETURN sequence;
	END
	$func$ LANGUAGE plpgsql;

view raw getsequence.sql hosted with ❤ by GitHub

I invoke this function from my application where I pass a year as the sequence name parameter:

	var year="2018";
	using (var cmd = new NpgsqlCommand("public.f_next_seq"))
	{
	cmd.Connection = _documentSession.Connection;
	cmd.CommandType = System.Data.CommandType.StoredProcedure;
	cmd.Parameters.AddWithValue("@_seq", NpgsqlTypes.NpgsqlDbType.Text,year);
	return Convert.ToInt32(cmd.ExecuteScalar());
	}

view raw generatenextsequence.cs hosted with ❤ by GitHub