_ltree (ltree[]) fail to register

[aight8]

I currently have the problem that I don't can make _ltree (array of ltree values) to work.

I tried with pgxtype.LoadDataType and also manually define by the actual oid of ltree (after extension is loaded previously).

array element OID not registered as ValueTranscoder

Any ideas - especially how to successfully use _ltree (ltree[])?

Ltree works by the way by just pgtype.GenericText and also assignable the string default pg type.

[jackc]

I have not tried it with ltree but for my custom types I do something like this.

	dataTypeNames := []string{
		"foo",
		"_foo",
	}

	for _, typeName := range dataTypeNames {
		dataType, err := pgxtype.LoadDataType(ctx, conn, conn.ConnInfo(), typeName)
		if err != nil {
			return err
		}
		conn.ConnInfo().RegisterDataType(dataType)
	}

[jschaf]

Hello from https://github.com/jschaf/pggen/issues/12.

On a related note, I'm trying to figure out why using pgtype.TextArray as a prepared expression that's eventually converted to ltree[] has different behavior between these two queries. Not urgent, I'm looking to understand is all.

1. Errors: SELECT $1::ltree[] AS direct_arr; with wrong element type (SQLSTATE 42804)

2. Works: SELECT ($1::text[])::ltree[] AS text_arr;

I'd guess it's related to the fact that ($1::text[])::ltree[] sends Postgres text[] and Postgres converts it to ltree[] once it starts executing the query. With $1::ltree[], Postgres expects the wire type to be ltree[] but we're sending text[].

package ltree

import (
	"context"
	"github.com/jackc/pgtype"
	"github.com/jschaf/pggen/internal/pgtest"
	"testing"
)

func TestTypeTest_DirectArr(t *testing.T) {
	conn, cleanup := pgtest.NewPostgresSchema(t, []string{"schema.sql"})
	defer cleanup()
	ctx := context.Background()

	const query = `SELECT $1::ltree[] AS direct_arr;`

	arr := []string{"qux", "qux"}
	row := conn.QueryRow(ctx, query, newTextArray(arr))
	out := pgtype.TextArray{}
	if err := row.Scan(&out); err != nil {
		t.Fatal(err)
	}
}

func TestTypeTest_TextArr(t *testing.T) {
	conn, cleanup := pgtest.NewPostgresSchema(t, []string{"schema.sql"})
	defer cleanup()
	ctx := context.Background()

	const query = `SELECT ($1::text[])::ltree[] AS text_arr;`

	arr := []string{"qux", "qux"}
	row := conn.QueryRow(ctx, query, newTextArray(arr))
	out := pgtype.TextArray{}
	if err := row.Scan(&out); err != nil {
		t.Fatal(err)
	}
}

// newTextArray creates a one dimensional text array from the string slice with
// no null elements.
func newTextArray(ss []string) pgtype.TextArray {
	elems := make([]pgtype.Text, len(ss))
	for i, s := range ss {
		elems[i] = pgtype.Text{String: s, Status: pgtype.Present}
	}
	return pgtype.TextArray{
		Elements:   elems,
		Dimensions: []pgtype.ArrayDimension{{Length: int32(len(ss)), LowerBound: 1}},
		Status:     pgtype.Present,
	}
}

[jackc]

I'd guess it's related to the fact that ($1::text[])::ltree[] sends Postgres text[] and Postgres converts it to ltree[] once it starts executing the query. With $1::ltree[], Postgres expects the wire type to be ltree[] but we're sending text[].

You are correct.