How do I create an index about pg HOT 9 CLOSED

type User struct {
	ID          int64
	Name   string
	Emails []string
}

type Story struct {
	ID             int64
	Title         string
	AuthorID int64 `cpg:"index:btree"`
	Author   *User
}

func createSchema(db *pg.DB) error {
	models := []interface{}{
		&User{},
		&Story{},
	}

	// Create Tables
	err = CreateTable(db, models)
	if err != nil {
		return err
	}

	return nil
}

// CreateTable ...
func CreateTable(db *pg.DB, models []interface{}) error {
	for _, model := range models {
		err := db.CreateTable(model, &orm.CreateTableOptions{
			IfNotExists: true,
		})
		if err != nil {
			return err
		}

		err = handleTagCpg(db, model, nil)
		if err != nil {
			return err
		}
	}

	return nil
}

// rootModel is for handling nested anonymous structs inside a table struct
func handleTagCpg(db *pg.DB, model interface{}, rootModel interface{}) error {
	if rootModel == nil {
		rootModel = model
	}
	t := reflect.TypeOf(model).Elem()
	for i := 0; i < t.NumField(); i++ {
		field := t.Field(i)
		if field.Anonymous {
			err := handleTagCpg(db, reflect.New(field.Type).Interface(), rootModel)
			if err != nil {
				return err
			}
			continue
		}
		tag := field.Tag.Get("cpg")
		if tag != "" {
			tagSplit := strings.Split(tag, ":")
			if tagSplit[0] == "index" {
				err := CreateIndex(db, rootModel, tagSplit[1], Underscore(field.Name))
				if err != nil {
					return err
				}
			}
		}
	}
	return nil
}

// Underscore converts "CamelCasedString" to "camel_cased_string".
func Underscore(s string) string {
	r := make([]byte, 0, len(s)+5)
	for i := 0; i < len(s); i++ {
		c := s[i]
		if IsUpper(c) {
			if i > 0 && i+1 < len(s) && (IsLower(s[i-1]) || IsLower(s[i+1])) {
				r = append(r, '_', ToLower(c))
			} else {
				r = append(r, ToLower(c))
			}
		} else {
			r = append(r, c)
		}
	}
	return string(r)
}

// IsUpper ...
func IsUpper(c byte) bool {
	return c >= 'A' && c <= 'Z'
}

// IsLower ...
func IsLower(c byte) bool {
	return c >= 'a' && c <= 'z'
}

// ToUpper ...
func ToUpper(c byte) byte {
	return c - 32
}

// ToLower ...
func ToLower(c byte) byte {
	return c + 32
}

// GetTableName ...
func GetTableName(db *pg.DB, model interface{}) (string, error) {
	var tableName string
	// Extract the table name from the model
	_, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
	if err != nil {
		return "", err
	}

	// I am paranoid and check if tableName is quoted string
	if tableName[0:1] != `"` || tableName[len(tableName)-1:len(tableName)] != `"` {
		return "", errors.New("tableName format returned from postgres is not correct")
	}
	// Trim the quotes which by default will look like -> "table_name"
	tableName = tableName[1 : len(tableName)-1]
	return tableName, nil
}

// CreateIndex ...
func CreateIndex(db *pg.DB, model interface{}, indexType string, indexColumn string) error {
	tableName, err := GetTableName(db, model)
	if err != nil {
		return err
	}

	_, err = db.Model(model).Exec(
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS %s_%s_idx ON %s USING %s (%s)", tableName, indexColumn, tableName, indexType, indexColumn),
	)
	if err != nil {
		return err
	}
	return nil
}

func createSchema(db *pg.DB) (err error) {
	for _, model := range []interface{}{(*APIChannel)(nil), (*Mgmt)(nil), (*PlatformAccount)(nil)} {
		err = db.CreateTable(model, &orm.CreateTableOptions{
			IfNotExists: true,
		})
		if err != nil {
			return err
		}

		// Extract the table name from the model
		var tableName string
		_, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
		if err != nil {
			panic(err)
		}
		// Trim the quotes which by default will look like -> "table_name"
		tableName = tableName[1 : len(tableName)-1]

		// Create an index on "deleted_at" column of each table to support the soft delete functionality of the BaseModel
		_, err = db.Exec(fmt.Sprintf("CREATE INDEX %s_deleted_at ON %s (deleted_at)", tableName, tableName))
		if err != nil {
			panic(err)
		}
	}
	return nil
}

type Base struct {
	ID        int64
	CreatedAt time.Time `sql:"default:now()"`
	UpdatedAt time.Time `sql:"default:now()"`
}

// BeforeUpdate ... 
func (b *Base) BeforeUpdate(ctx context.Context) (context.Context, error) {
	b.UpdatedAt = time.Now()
	return ctx, nil
}

var _ orm.BeforeUpdateHook = (*Base)(nil)

// Order ...
type Order struct {
	Base
	Name  string
	Items []Item `pg:"many2many:order_to_items"`
}

// Item ...
type Item struct {
	Base
	Name string
}

// OrderToItem ...
type OrderToItem struct {
	OrderID int64
	ItemID  int64
}

func createSchema(db *pg.DB) error {
	// Register many to many model so ORM can better recognize m2m relation.
	orm.RegisterTable((*OrderToItem)(nil))

	models := []interface{}{
		&Order{},
		&Item{},
		&OrderToItem{},
	}

	// Drop Tables For Testing (Don't do it in production)
	err := DropTable(db, models)
	if err != nil {
		return err
	}

	// Create Tables (will create created_at index automatically)
	err = CreateTable(db, models)
	if err != nil {
		return err
	}

	err = CreateIndex(db, (*OrderToItem)(nil), "btree", "order_id")
	if err != nil {
		return err
	}
	err = CreateIndex(db, (*OrderToItem)(nil), "btree", "item_id")
	if err != nil {
		return err
	}

	return nil
}

// CreateTable ...
func CreateTable(db *pg.DB, models []interface{}) error {
	for _, model := range models {
		err := db.CreateTable(model, &orm.CreateTableOptions{
			IfNotExists: true,
		})
		if err != nil {
			return err
		}

		// Create btree index on created_at column if exists
		if reflect.ValueOf(model).Elem().FieldByName("CreatedAt").IsValid() {
			err := CreateIndex(db, model, "btree", "created_at")
			if err != nil {
				return err
			}
		}
	}

	return nil
}

// DropTable ...
func DropTable(db *pg.DB, models []interface{}) error {
	for _, model := range models {
		err := db.DropTable(model, &orm.DropTableOptions{
			IfExists: true,
		})
		if err != nil {
			return err
		}
	}

	return nil
}

// GetTableName ...
func GetTableName(db *pg.DB, model interface{}) (string, error) {
	var tableName string
	// Extract the table name from the model
	_, err := db.Model(model).Query(&tableName, "SELECT '?TableName'")
	if err != nil {
		return "", err
	}

	// I am paranoid and check if tableName is quoted string
	if tableName[0:1] != `"` || tableName[len(tableName)-1:len(tableName)] != `"` {
		return "", errors.New("tableName format returned from postgres is not correct")
	}
	// Trim the quotes which by default will look like -> "table_name"
	tableName = tableName[1 : len(tableName)-1]
	return tableName, nil
}

// CreateIndex ...
func CreateIndex(db *pg.DB, model interface{}, indexType string, indexColumn string) error {
	tableName, err := GetTableName(db, model)
	if err != nil {
		return err
	}

	_, err = db.Model(model).Exec(
		fmt.Sprintf("CREATE INDEX IF NOT EXISTS %s_%s_idx ON %s USING %s (%s)", tableName, indexColumn, tableName, indexType, indexColumn),
	)
	if err != nil {
		return err
	}
	return nil
}