Can't get UUID via output parameter from stored procedure

[q0dr]

I'm trying to use a stored procedure that returns a UUID output parameter:

CREATE PROCEDURE create_team (
    OUT tid UUID,
    IN team_name STRING,
    IN active_directory_group STRING,
    IN created_by STRING
)
LANGUAGE PLpgSQL
AS $$
BEGIN
  INSERT INTO teams (team_name, created_by, last_modified_by, active_directory_group)
    SELECT team_name, created_by, created_by, active_directory_group
  RETURNING team_id INTO tid;
END;
$$;

I invoked it from Golang using:

func main() {
	dbconfig, err := pgxpool.ParseConfig("postgresql://root@localhost:26257/ai_dev_studio_dev?sslmode=disable")
	if err != nil {
		fmt.Println("could not parse db config: ", err)
		return
	}

	ctx := context.Background()
	dbconfig.AfterConnect = func(_ context.Context, conn *pgx.Conn) error {
		pgxuuid.Register(conn.TypeMap())
		return nil
	}

	pool, err := pgxpool.NewWithConfig(ctx, dbconfig)
	if err != nil {
		fmt.Println("could not create pool: ", err)
		return
	}
	defer pool.Close()

	var team_id uuid.UUID
	random_team_name := uuid.NewGen()

	_, err = pool.Exec(ctx, "CALL create_team($1, $2, $3, $4)", sql.Named("team_id", sql.Out{Dest: &team_id}), random_team_name, "team ad group", "rgenter")
	if err != nil {
		log.Fatalf("conn.Exec: %v", err)
	}
	log.Printf("team_id: %v", team_id)
}

I get:

2025/04/29 14:43:12 conn.Exec: failed to encode args[0]: unable to encode sql.NamedArg{_NamedFieldsRequired:struct {}{}, Name:"team_id", Value:sql.Out{_NamedFieldsRequired:struct {}{}, Dest:[16]uint8{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, In:false}} into binary format for uuid (OID 2950): cannot find encode plan

I found issue #2107 addressed a similar problem for SELECT and INSERT. Are UUID output parameters not supported?

[q0dr]

I figured out the solution. I'm documenting it here because it was not intuitive.

I made the following changes:

• I initialized team_id to a valid UUID (00000000-0000-0000-0000-000000000000)
• Instead of using pool.Exec I used pool.QueryRow and then called Scan on the Row returned.

The resulting program:

package main

import (
	"context"
	"log"

	"github.com/gofrs/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	pgxuuid "github.com/jackc/pgx-gofrs-uuid"
)

func main() {
	dbconfig, err := pgxpool.ParseConfig("postgresql://root@localhost:26257/ai_dev_studio_dev?sslmode=disable")
	if err != nil {
		log.Fatalf("could not parse db config: %v", err)
	}

	ctx := context.Background()
	dbconfig.AfterConnect = func(_ context.Context, conn *pgx.Conn) error {
		pgxuuid.Register(conn.TypeMap())
		return nil
	}

	pool, err := pgxpool.NewWithConfig(ctx, dbconfig)
	if err != nil {
		log.Fatalf("could not create pool: %v", err)
	}
	defer pool.Close()

	team_id, err := uuid.FromString("00000000-0000-0000-0000-000000000000")
	if err != nil {
		log.Fatalf("uuid.FromString: %v", err)
	}
	random_team_name, err := uuid.NewV4()
	if err != nil {
		log.Fatalf("uuid.NewV4: %v", err)
	}

	err = pool.QueryRow(ctx, "CALL create_team($1, $2, $3, $4)", team_id, random_team_name, "team ad group", "rgenter").Scan(&team_id)
	if err != nil {
		log.Fatalf("pool.QueryRow.Scan: %v", err)
	}
	log.Printf("team_id: %v", team_id)
}

worked.

The unintuitive parts were
a) Having to pass a valid UUID in to team_id, even though team_id is an OUT parameter, and
b) Using QueryRow and getting the output parameter as a Row that has to be scanned.

I hope this helps someone else trying to figure this out.