CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(table_name text, column_name text)

RETURNS void

LANGUAGE plpgsql

AS $$

DECLARE -- will hold the max value found in the sequence max_id integer; -- sequence name sequence_name text := table_name || '_' || column_name || '_seq';

BEGIN -- check sequence IF NOT EXISTS (SELECT 0 FROM pg_class where relname = sequence_name) THEN RAISE EXCEPTION 'sequence does not exist: %', sequence_name; END IF; -- lock table EXECUTE format('LOCK %I;', table_name); -- get current maximum id EXECUTE format ( 'SELECT COALESCE(MAX(%I), 0) FROM %I;', column_name, table_name ) INTO max_id; -- drop the sequence EXECUTE format('DROP SEQUENCE %I CASCADE;', sequence_name); -- alter table so it now uses an identity, instead of the previously sequence EXECUTE format ( 'ALTER TABLE %I ALTER COLUMN %I ADD GENERATED BY DEFAULT AS IDENTITY (START WITH %s)', table_name, column_name, max_id + 1 );

END;

$$