pg_hashids trigger test

main.go

package main

import (
	"fmt"
	"log"

	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq" //import pq
)

// create table xy
// (
//   id serial not null,
//   hash character varying(10),
//   constraint xy_pkey primary key (id)
// )
// with (
//   oids=false
// );
//
// CREATE FUNCTION xy_create_hash() RETURNS trigger AS $$
//     BEGIN
//         NEW.hash := id_encode(NEW.id, 'mysalt', 6);
//         RETURN NEW;
//     END;
// $$ LANGUAGE plpgsql;
//
// CREATE TRIGGER create_xy_hash BEFORE INSERT ON xy FOR EACH ROW EXECUTE PROCEDURE xy_create_hash();

type XYRow struct {
	ID   int64  `db:"id"`
	Hash string `db:"hash"`
}

func insert(tx *sqlx.Tx) (*XYRow, error) {
	var id int64

	row := &XYRow{Hash: ""} // should be set by trigger
	query := `insert into xy (hash) values (:hash) returning id;`

	r, err := tx.NamedQuery(query, row)
	if err != nil {
		log.Fatalf("namedquery fail. err: %v", err)
	}

	r.Next()
	err = r.Scan(&id)
	_ = r.Close()
	if err != nil {
		log.Fatalf("scan fail. err: %v", err)
	}

	row.ID = id
	return row, err
}

func main() {
	con := fmt.Sprintf("user=%s password=%s dbname=%s port=%d sslmode=disable",
		"hash-test", "hash-test", "hash-test", 5432)
	db, err := sqlx.Open("postgres", con)
	if err != nil {
		log.Fatal(err)
	}

	n := 1 // 1 shows the issue, > 1 is ok
	for i := 0; i < n; i++ {
		tx, err := db.Beginx()
		if err != nil {
			log.Fatalf("failed to create tx Err: %v", err)
		}

		xr, err := insert(tx)
		if err != nil {
			log.Fatalf("failed to create row Err: %v", err)
		}

		if err := tx.Commit(); err != nil {
			log.Fatalf("failed to commit Err: %v", err)
		}
		log.Println(xr)
	}
}

Simpler pure standard library reduction:

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq" //import pq
)

// create table xy
// (
//   id serial not null,
//   hash character varying(10),
//   constraint xy_pkey primary key (id)
// )
// with (
//   oids=false
// );
//
// CREATE or REPLACE FUNCTION xy_create_hash() RETURNS trigger AS $$
//     BEGIN
//         NEW.hash := id_encode(NEW.id, 'mysalt', 6);
//         RETURN NEW;
//     END;
// $$ LANGUAGE plpgsql;
//
// CREATE TRIGGER create_xy_hash BEFORE INSERT ON xy FOR EACH ROW EXECUTE PROCEDURE xy_create_hash();

func main() {
	con := fmt.Sprintf("user=%s password=%s dbname=%s port=%d sslmode=disable",
		"hash-test", "hash-test", "hash-test", 5432)
	db, err := sql.Open("postgres", con)
	if err != nil {
		log.Fatal(err)
	}

	n := 1 // 1 shows the issue, > 1 is ok
	for i := 0; i < n; i++ {
		var id int64
		r := db.QueryRow("insert into xy (hash) values ('') returning id;")
		err := r.Scan(&id)
		if err != nil {
			log.Fatalf("scan fail. err: %v", err)
		}
		log.Println(id)
	}
}