-
Notifications
You must be signed in to change notification settings - Fork 26
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
array_agg(table) returning a []Table? #15
Comments
Oh, interesting. If I understand correctly, the query builds an array of the entire composite type of Can you provide the pggen command that you used? I'm able to reproduce the bug. I'll get cracking on fixing it. |
A few slightly off-topic comments not related to pggen.
|
sure it's
😍 brilliant thanks! i'd be very excited to use pggen then, I've just started experimenting with it yesterday and this feature would really seal the deal for me :)
this is pg_trgm syntax
I will do this, thanks 👌
I was thinking of this one too, but my project is still in it's early days and I'm stilling figuring out a nice stack first |
This is partially resolved by 29b8bc8. That commit fixes the generated code so it least compiles. It won't run however. Coaxing pgx to scan an array of composite types is difficult to do without knowing the OIDs ahead of time. If you implement the Decode methods for I have a working sketch so that pggen will generate working code. It depends on a few things:
// SearchScreenshots implements Querier.SearchScreenshots.
func (q *DBQuerier) SearchScreenshots(ctx context.Context, params SearchScreenshotsParams) ([]SearchScreenshotsRow, error) {
rows, err := q.conn.Query(ctx, searchScreenshotsSQL, params.Body, params.Limit, params.Offset)
if rows != nil {
defer rows.Close()
}
if err != nil {
return nil, fmt.Errorf("query SearchScreenshots: %w", err)
}
items := []SearchScreenshotsRow{}
blockRow, err := pgtype.NewCompositeType("blocks", []pgtype.CompositeTypeField{
{Name: "id", OID: pgtype.Int4OID},
{Name: "screenshot_id", OID: pgtype.Int8OID},
{Name: "body", OID: pgtype.TextOID},
}, q.conn.(*pgx.Conn).ConnInfo())
if err != nil {
return nil, fmt.Errorf("build composite type block: %w", err)
}
blockArray := pgtype.NewArrayType("_block", 0, func() pgtype.ValueTranscoder {
return blockRow.NewTypeValue().(*pgtype.CompositeType)
})
for rows.Next() {
var item SearchScreenshotsRow
if err := rows.Scan(&item.ID, blockArray); err != nil {
return nil, fmt.Errorf("scan SearchScreenshots row: %w", err)
}
blockArray.AssignTo(&item.Blocks)
items = append(items, item)
}
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("close SearchScreenshots rows: %w", err)
}
return items, err
} |
This should be fixed in 2dd57b9 and ad75bc3. Give it a go and re-open if you run into any issues. There's a tested example in https://github.com/jschaf/pggen/tree/main/example/composite This is one of the more brittle implementations that pggen supports. There's a few caveats:
|
amazing! this is great news. i'll be trying it out very shortly |
this works really well, thanks very much :) |
hi thanks for the cool tool!
given a schema
and query
(
'str' % column
being apg_trgm
thing)pggen generates
however the
Blocks
in SearchScreenshotsRow's[]Blocks
is undefinedso my question is,
is it somehow possible, or in the scope of this project, that pggen could generate something like
where
Block
is a struct with fields matching theblocks
table?also postgres shows the type of the
![image](https://user-images.githubusercontent.com/6832539/110039633-708f9d00-7d39-11eb-898c-a57dd3aa940d.png)
array_agg(blocks)
column asblock[]
any help is much appreciated, thanks!
The text was updated successfully, but these errors were encountered: