Skip to content

Select from views and array_agg #3071

Open
@mariusavos

Description

@mariusavos

Version

1.24.0

What happened?

We have a number of views that are created by joining tables, grouping, and array_agging some columns. Sqlc generated the right code previously, but if you turn on database-backed query analysis then while the model for the view is still correct, select queries from the view don't return that model and instead return a different and also otherwise incorrect type.

I.e. in the playground example below,

type BooksWithAuthor struct {
	Books []string
	ID    int64
	Name  string
	Bio   sql.NullString
}

is correct, but the select query instead returns

type GetAuthorBooksRow struct {
	Books string
	ID    int64
	Name  string
	Bio   sql.NullString
}

which is not only different, but also has an incorrect type for Books.

Relevant log output

No response

Database schema

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

create table books (
  id BIGSERIAL PRIMARY KEY,
  name text NOT NULL,
  author BIGSERIAL references authors(id)
 );
 
 create view books_with_author as 
 	select array_agg(books.name)::text[] as books, authors.* from books
    join authors on authors.id = books.author
    group by authors.id;

SQL queries

-- name: GetAuthorBooks :one
SELECT * FROM books_with_author;

Configuration

{
  "version": "2",
  "cloud": {
  	"project":"<PROJECT_ID>"
  },
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "database" :{
    	"managed": true
    },
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/9d855f57e257cbd5acd956f1bc9d5b1f55ada9604602b5acd1742dd49337e289

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions