[go: up one dir, main page]

Skip to content
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

JSONB "-- name:XXX :one" Get wrong type. many action is ok. #3724

Open
piyongcai-liucai opened this issue Nov 26, 2024 · 0 comments
Open

JSONB "-- name:XXX :one" Get wrong type. many action is ok. #3724

piyongcai-liucai opened this issue Nov 26, 2024 · 0 comments
Labels
📚 postgresql bug Something isn't working

Comments

@piyongcai-liucai
Copy link

Version

1.27.0

What happened?

A bug happened!

  1. database: postgres 16.x

  2. custom type

type FundSummary struct {
	// ...
        X        string          `json:"bank"`         // 银行
	// ...
}
  1. sqlc generate. GetFundSummary get wrong extend type.
const getFundSummary = `-- name: GetFundSummary :one
SELECT id,
       account,
       name,
       extent
  FROM auth_user
 WHERE id = $1
 LIMIT 1
`

type GetFundSummaryRow struct {
	ID      int32           `json:"id"`
	Account string          `json:"account"`
	Name    string          `json:"name"`
	Extent  json.RawMessage `json:"extent"`                    // <--------- WRONG TYPE
}

func (q *Queries) GetFundSummary(ctx context.Context, id int32) (*GetFundSummaryRow, error) {
	row := q.db.QueryRow(ctx, getFundSummary, id)
	var i GetFundSummaryRow
	err := row.Scan(
		&i.ID,
		&i.Account,
		&i.Name,
		&i.Extent,
	)
	return &i, err
}

const selectFundSummary = `-- name: SelectFundSummary :many
SELECT name,
       extent
  FROM auth_user
 WHERE user_type = 'manager'
  ORDER BY name
`

type SelectFundSummaryRow struct {
	Name   string             `json:"name"`
	Extent schema.FundSummary `json:"extent"`     // <---- BUT HERE GET CORRECT TYPE
}

func (q *Queries) SelectFundSummary(ctx context.Context) ([]*SelectFundSummaryRow, error) {
	rows, err := q.db.Query(ctx, selectFundSummary)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []*SelectFundSummaryRow
	for rows.Next() {
		var i SelectFundSummaryRow
		if err := rows.Scan(&i.Name, &i.Extent); err != nil {
			return nil, err
		}
		items = append(items, &i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Relevant log output

const getFundSummary = `-- name: GetFundSummary :one
SELECT id,
       account,
       name,
       extent
  FROM auth_user
 WHERE id = $1
 LIMIT 1
`

type GetFundSummaryRow struct {
	ID      int32           `json:"id"`
	Account string          `json:"account"`
	Name    string          `json:"name"`
	Extent  json.RawMessage `json:"extent"`                    // <--------- WRONG TYPE
}

func (q *Queries) GetFundSummary(ctx context.Context, id int32) (*GetFundSummaryRow, error) {
	row := q.db.QueryRow(ctx, getFundSummary, id)
	var i GetFundSummaryRow
	err := row.Scan(
		&i.ID,
		&i.Account,
		&i.Name,
		&i.Extent,
	)
	return &i, err
}

const selectFundSummary = `-- name: SelectFundSummary :many
SELECT name,
       extent
  FROM auth_user
 WHERE user_type = 'manager'
  ORDER BY name
`

type SelectFundSummaryRow struct {
	Name   string             `json:"name"`
	Extent schema.FundSummary `json:"extent"`     // <---- BUT HERE GET CORRECT TYPE
}

func (q *Queries) SelectFundSummary(ctx context.Context) ([]*SelectFundSummaryRow, error) {
	rows, err := q.db.Query(ctx, selectFundSummary)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []*SelectFundSummaryRow
	for rows.Next() {
		var i SelectFundSummaryRow
		if err := rows.Scan(&i.Name, &i.Extent); err != nil {
			return nil, err
		}
		items = append(items, &i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Database schema

CREATE TABLE IF NOT EXISTS auth_user
(
    id      SERIAL  PRIMARY KEY, -- ID
    name    VARCHAR NOT NULL,   
    user_type VARCHAR NOT NULL,
    extent  JSONB   NOT NULL DEFAULT '{}', --
    dept_id INTEGER              -- 部门ID
);

SQL queries

-- name: SelectFundSummary :many
SELECT name,
       extent
  FROM auth_user
 WHERE user_type = 'manager'
  ORDER BY name; 

-- name: GetFundSummary :one
SELECT id,
       account,
       name,
       extent
  FROM auth_user
 WHERE id = $1
 LIMIT 1;

Configuration

version: 2
sql:
  - queries: sql
    engine: postgresql
    database:
      uri: postgresql://postgres:123@localhost:5432/postgres?search_path=zjbmis
    schema: sql/schema
    gen:
      go:
        sql_package: pgx/v5
        emit_json_tags: true
        emit_db_tags: false
        emit_interface: false
        emit_result_struct_pointers: true
        emit_pointers_for_null_types: true
        emit_params_struct_pointers: true
        emit_enum_valid_method: true
        emit_all_enum_values: true
        package: crud
        out: crud
        overrides:
          - column: auth_user.extent
            go_type: liucai.com/zjbmis/schema.FundSummary

Playground URL

No response

What operating system are you using?

No response

What database engines are you using?

No response

What type of code are you generating?

No response

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
📚 postgresql bug Something isn't working
Projects
None yet
Development

No branches or pull requests

1 participant