CreateSequenceForIds.sql

do $$
declare
  seq_name text;
  tbl information_schema.tables%rowtype;
  tables CURSOR FOR SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;
begin
  FOR table_record IN tables LOOP
    seq_name = table_record.tablename || '_id_seq';
    -- tests if a sequence with this name already exists
    IF EXISTS (SELECT 0 FROM pg_class where relname = seq_name)
    THEN
      raise warning '% already exists. Skipped.', seq_name;
    ELSE
      -- tests if id column exists for current table
      IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name=table_record.tablename and column_name='id')
      THEN
        EXECUTE 'create sequence ' || seq_name;
        EXECUTE 'alter sequence ' || seq_name || ' owned by ' || table_record.tablename || '.id';
        EXECUTE 'alter table ' || table_record.tablename || ' alter id set default nextval(''' || seq_name || ''')';
        raise info '% successfully created!', seq_name;
      END IF;
    END IF;
  END LOOP;
end;
$$language plpgsql;