database/sql: add support for multiple result sets

Many database systems allow returning multiple result sets
in a single query. This can be useful when dealing with many
intermediate results on the server and there is a need
to return more then one arity of data to the client.

Fixes #12382

Change-Id: I480a9ac6dadfc8743e0ba8b6d868ccf8442a9ca1
Reviewed-on: https://go-review.googlesource.com/30592
Reviewed-by: Brad Fitzpatrick <bradfitz@golang.org>
Run-TryBot: Brad Fitzpatrick <bradfitz@golang.org>
TryBot-Result: Gobot Gobot <gobot@golang.org>

1 files changed, 62 insertions, 0 deletions

diff --git a/src/database/sql/example_test.go b/src/database/sql/example_test.go
index dcb74e0699..9032eac2d2 100644
--- a/src/database/sql/example_test.go
+++ b/src/database/sql/example_test.go
@@ -44,3 +44,65 @@ func ExampleDB_QueryRow() {
 		fmt.Printf("Username is %s\n", username)
 	}
 }
+
+func ExampleDB_QueryMultipleResultSets() {
+	age := 27
+	q := `
+create temp table uid (id bigint); -- Create temp table for queries.
+insert into uid
+select id from users where age < ?; -- Populate temp table.
+
+-- First result set.
+select
+	users.id, name
+from
+	users
+	join uid on users.id = uid.id
+;
+
+-- Second result set.
+select 
+	ur.user, ur.role
+from
+	user_roles as ur
+	join uid on uid.id = ur.user
+;
+	`
+	rows, err := db.Query(q, age)
+	if err != nil {
+		log.Fatal(err)
+	}
+	defer rows.Close()
+
+	for rows.Next() {
+		var (
+			id   int64
+			name string
+		)
+		if err := rows.Scan(&id, &name); err != nil {
+			log.Fatal(err)
+		}
+		fmt.Printf("id %d name is %s\n", id, name)
+	}
+	if !rows.NextResultSet() {
+		log.Fatal("expected more result sets", rows.Err())
+	}
+	var roleMap = map[int64]string{
+		1: "user",
+		2: "admin",
+		3: "gopher",
+	}
+	for rows.Next() {
+		var (
+			id   int64
+			role int64
+		)
+		if err := rows.Scan(&id, &role); err != nil {
+			log.Fatal(err)
+		}
+		fmt.Printf("id %d has role %s\n", id, roleMap[role])
+	}
+	if err := rows.Err(); err != nil {
+		log.Fatal(err)
+	}
+}