Feature: SELECT with nested structs

[td0m]

In SQLX, the following syntax is correct:

SELECT
  mac,
  name,
  "user".id "user.id",
  "user".name "user.name"
FROM device
LEFT JOIN "user"
  ON "user".id=device.user_id;

and can be scanned into a model such as:

type Device struct {
  Mac string
  Name string
  User *struct {
    ID string
    Name string
  }
}

Doing this in pggen tries to generate struct fields containing a dot, which is not a valid format.

Are you planning to add support for such scans with sub structs?

[jschaf]

Yea, pggen has at least 1 bug here by not escaping invalid character in identifiers.

Overall, I don't like the magical dot syntax to create a new anonymous struct. I'd be willing to support composite types which accomplish the same task. I think composite types are a better solution since you can also use a named struct instead of an anonymous struct.

SELECT d.mac,
       d.name,
       ROW (u.id, u.name)::"user"
FROM device d
  LEFT JOIN "user" u
            ON u.id = d.user_id;

Ultimately, this is an extension of custom type support so I'll land that first.

[jschaf]

Doing this in pggen tries to generate struct fields containing a dot, which is not a valid format.

This particular bug should be fixed. pggen should generate valid Go identifiers for all output columns and params.

[jschaf]

Ok, I've landed initial support for Composite types. There's a few limitations which I'll work on:

• Composite type resolution is only 1 level deep. If you have a composite-type within a composite type, it'll probably fail to find a Go type.
• Composite type support only works for output columns, not input parameters. Seems pretty niche to use a full composite type as an input param. Using an ID of a composite type is much more common.

The following query:

-- name: CompositeUser :many
SELECT
  d.mac,
  d.type,
  ROW (u.id, u.name)::"user" AS "user"
FROM device d
  LEFT JOIN "user" u ON u.id = d.owner;

Generates the Go code:

// User represents the Postgres composite type "user".
type User struct {
    ID   pgtype.Int8
    Name pgtype.Text
}

type CompositeUserRow struct {
    Mac  pgtype.Macaddr `json:"mac"`
    Type DeviceType     `json:"type"`
    User User           `json:"user"`
}

// CompositeUser implements Querier.CompositeUser.
func (q *DBQuerier) CompositeUser(ctx context.Context) ([]CompositeUserRow, error) {
    rows, err := q.conn.Query(ctx, compositeUserSQL)
    if rows != nil {
        defer rows.Close()
    }
    if err != nil {
        return nil, fmt.Errorf("query CompositeUser: %w", err)
    }
    items := []CompositeUserRow{}
    userRow := pgtype.CompositeFields([]interface{}{
        &pgtype.Int8{},
        &pgtype.Text{},
    })
    for rows.Next() {
        var item CompositeUserRow
        if err := rows.Scan(&item.Mac, &item.Type, userRow); err != nil {
            return nil, fmt.Errorf("scan CompositeUser row: %w", err)
        }
        item.User.ID = *userRow[0].(*pgtype.Int8)
        item.User.Name = *userRow[1].(*pgtype.Text)
        items = append(items, item)
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return items, err
}

Implementing this was a fun adventure:

• Added a type hierarchy for Go types.
• Figured out how to use pgtype to decode composite types.
• Sanitized illegal names including keyword names like type and names that can't be represented in Go like names with all whitespace select 1 as " ".
• Bunch of tricky templating logic.