db/migrate/20231026050554_add_functions_for_primary_key_lookup.rb


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# frozen_string_literal: true

class AddFunctionsForPrimaryKeyLookup < Gitlab::Database::Migration[2.2]
  milestone '16.6'

  TABLES = %i[users namespaces projects].freeze

  def up
    TABLES.each do |table|
      execute <<~SQL
        CREATE OR REPLACE FUNCTION find_#{table}_by_id(#{table}_id bigint)
        RETURNS #{table} AS $$
        BEGIN
          return (SELECT #{table} FROM #{table} WHERE id = #{table}_id LIMIT 1);
        END;
        $$ LANGUAGE plpgsql STABLE PARALLEL SAFE COST 1;
      SQL
    end
  end

  def down
    TABLES.each do |table|
      execute "DROP FUNCTION IF EXISTS find_#{table}_by_id"
    end
  end
end