reset_sequences.sql

CREATE OR REPLACE FUNCTION reset_sequences()
RETURNS void
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE

	m_Record RECORD;

BEGIN

	FOR m_Record IN
	SELECT table_schema || '.' || table_name AS tab,
		table_schema || '.' || table_name || '.' || column_name AS col,
		table_schema || '.' || table_name || '_' || column_name || '_seq' AS seq
	FROM information_schema.columns
	WHERE information_schema.columns.column_default ILIKE 'NEXTVAL%'
	LOOP
  
	EXECUTE '
		SELECT SETVAL( ' || QUOTE_LITERAL( m_Record.seq ) || ',
		(
			SELECT COALESCE( MAX( ' || QUOTE_IDENT( m_Record.col ) || ' ) + 1, 1 )
			FROM ' || QUOTE_IDENT( m_Record.tab ) || '
			WHERE ' || QUOTE_IDENT( m_Record.col ) || ' > 0 )
		)
	';

	END LOOP;

END;
$$;