How can I get the results of 2 select statements when running an sql file

[jbarisan]

My requirement is that I cannot change what is in the sql file that is provided to me. If I have an sql file that I read into a string I am not getting the results from the second select

main.go

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
)

func main() {

		query1 := `
		SELECT * FROM mytable;
	DO $$
	DECLARE s record;
	BEGIN
		FOR s IN SELECT name FROM mytable
	           LOOP
			RAISE NOTICE 'Name: %', s;
	       END LOOP;
	   END;
	$$
	;;
	SELECT name FROM mytable;
	`
		RunQuery(query1)
	// query2 := `select * from mytable;
	// select id from mytable;`
	// RunQuery(query2)
}

// Triggered with RAISE NOTICE
func onNotify(c *pgconn.PgConn, n *pgconn.Notice) {
	myresults.notices = append(myresults.notices, *&n.Message)
}

type Results struct {
	allRows [][]string
	notices []string
}

func PrintResults(r Results) {
	for _, v := range r.allRows {
		fmt.Println(v)
	}
	fmt.Println("")
	for _, v := range r.notices {
		fmt.Println(v)
	}
}

var myresults Results

func RunQuery(query string) (Results, error) {
	user := os.Getenv("dbuser")
	password := os.Getenv("dbpass")
	host := "localhost"
	port := "5432"
	dbname := "postgres"
	ctx := context.Background()
	connectionStr := fmt.Sprintf("user=%s password=%s host=%s port=%s dbname=%s sslmode=disable", user, password, host, port, dbname)

	conf, err := pgx.ParseConfig(connectionStr)
	if err != nil {
		fmt.Println("error parsing connection string: ", err)
		return myresults, err
	}

	conf.OnNotice = onNotify
	conn, err := pgx.ConnectConfig(ctx, conf)
	if err != nil {
		fmt.Println("error creating connection: ", err)
		return myresults, err
	}
	defer conn.Close(ctx)

	//use QueryExecModeExec or QueryExecModeSimpleProtocol as first arg after ctx, query
	response, qerr := conn.Query(ctx, query, pgx.QueryExecModeSimpleProtocol)
	if qerr != nil {
		fmt.Println("Need error handling", qerr)
		return myresults, err
	}

	var allRows [][]string
	var row []string
	for _, v := range response.FieldDescriptions() {
		row = append(row, v.Name)
	}

	allRows = append(allRows, row)
	raw := make([][]byte, len(row))       // byte slice is one column in the returned row. The slice of byte slice is to store multiple columns in one slice
	dest := make([]interface{}, len(row)) // slice of interface to insert unknown number of returned columns

	for i := range dest {
		dest[i] = &raw[i] // Use pointers to byte slices for Scan
	}

	for response.Next() {
		err := response.Scan(dest...)
		if err != nil {
			fmt.Println("unable to scan row")
			break
		}

		row := make([]string, len(allRows[0]))
		for i, v := range raw { // loop through slice of byte slice
			if v == nil {
				row[i] = "" // nil object returned from db. cannot have nil string
			} else {
				row[i] = string(v)
			}
		}
		allRows = append(allRows, row)
	}
	fmt.Println(allRows)
	response.Close()
	if response.Err() != nil {
		fmt.Println(response.Err().Error())
	}
	PrintResults(myresults)
	return myresults, nil
}

[jackc]

You will need to drop down to the pgconn layer to do that. Use https://pkg.go.dev/github.com/jackc/pgx/v5#Conn.PgConn to get access to that. Then execute your query with https://pkg.go.dev/github.com/jackc/pgx/[email protected]/pgconn#PgConn.Exec. It will return a MultiResultReader which you can use to iterate through the results. https://pkg.go.dev/github.com/jackc/pgx/v5#RowsFromResultReader can also be used to handle type conversion if you need more than the string format of the results.

[jbarisan]

Thank you this is working great now!