_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.