_ltree (ltree[]) not recognized

[aight8]

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

pggen gen go --go-type "ltree=github.com/jackc/pgtype.Text" \
  --go-type "_ltree=github.com/jackc/pgtype.TextArray"

infer output types for query: fetch oid types: find array types: find type for array elem _ltree oid=1266834

Any ideas?

[jschaf]

Thanks for the report! I'm having trouble reproducing. I added a tested example just now in e50555d. From the pggen repo, I can run the following successfully:

go build ./cmd/pggen 
DOCKER_API_VERSION=1.39 \
  ./pggen gen go \
    --schema-glob 'example/ltree/schema.sql' \
    --query-glob 'example/ltree/query.sql' \
    --go-type 'ltree=github.com/jackc/pgtype.Text' \
    --go-type '_ltree=github.com/jackc/pgtype.TextArray'

Here's the query file: https://github.com/jschaf/pggen/blob/main/example/ltree/query.sql

[aight8]

Ok i got it. The issue appears when I try to receive ltree[] as argument. While int[], text[] etc. array types work as excepted.

-- name: PggenTypeChecks2 :many
select
    pggen.arg('in_ltree')::ltree as ltree,
    pggen.arg('in_ltree_array')::ltree[] as ltree_array
;

btw: your example is missing the usage of ltree array but it is defined in the options.

---

There would be a quickfix solution by using pggen.arg('in_ltree_array')::text[]::ltree[] however I don't know why the usage of ltree[] directly (when defined) fails.

[jschaf]

btw: your example is missing the usage of ltree array but it is defined in the options.

I think that's covered by array_agg in this query. Let me know if not.

SELECT array_agg(path)
FROM test
WHERE path <@ 'Top.Science';

There would be a quickfix solution by using pggen.arg('in_ltree_array')::text[]::ltree[] however I don't know why the usage of ltree[] directly (when defined) fails.

I can't figure it out either. I added another exampe in f57582b to cover input examples.

-- name: FindLtreeInput :one
SELECT
  pggen.arg('in_ltree')::ltree                   AS ltree,
  -- This won't work, but I'm not quite sure why.
  -- Postgres errors with "wrong element type (SQLSTATE 42804)"
  -- All caps because we use regex to find pggen.arg and it confuses pggen.
  -- PGGEN.arg('in_ltree_array_direct')::ltree[]    AS direct_arr,

  -- The parenthesis around the text[] cast are important. They signal to pggen
  -- that we need a text array that Postgres then converts to ltree[].
  (pggen.arg('in_ltree_array')::text[])::ltree[] AS text_arr;

The only related pgx bug is https://github.com/jackc/pgtype/issues/75.

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

I'll ask the pgx maintainer but I'm not sure there's much I can do here.

[jschaf]

I'm not sure if we can do anything here. If I understand correctly, the desired behavior for --go-type "_ltree=github.com/jackc/pgtype.TextArray" on the following parameter:

pggen.arg('in_ltree')::ltree[]

is for pggen to take pgtype.TextArray as input and let postgres cast it ltree[]. pggen is fulfilling its end of the deal by passing a text array but it seems that Postgres wants an ltree array.

Maybe it's related to the fact that pgx uses prepared queries.