Add support for override of calculated column type

[aliml92]

What do you want to change?

At this moment, overriding the calculated column type is not possible. For example, I have the following query.sql:

-- Example queries for sqlc
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

CREATE TABLE books (
  id 	BIGSERIAL PRIMARY KEY,
  title text 	  NOT NULL
);

CREATE TABLE author_book (
  author_id BIGINT NOT NULL,
  book_id 	BIGINT NOT NULL,
  
  FOREIGN KEY (author_id) REFERENCES authors(id),
  FOREIGN KEY (book_id) REFERENCES books(id),
  PRIMARY KEY (author_id, book_id)
);

-- name: GetAuthor :one
SELECT 
	a.*,
	coalesce(array_agg(b.title)::text[], array[]::text[]) as book_list
FROM (
	SELECT *
    FROM authors
    WHERE name = $1
    LIMIT 1 
) a
LEFT JOIN author_book ab ON a.id = ab.author_id
LEFT JOIN books b ON ab.book_id = b.id
GROUP BY a.id
LIMIT 1;

Generated query.sql.go looks like the following:

// versions:
//   sqlc v1.17.2
// source: query.sql

package db

import (
	"context"
	"database/sql"
)

const getAuthor = `-- name: GetAuthor :one
SELECT 
	a.id, a.name, a.bio,
	coalesce(array_agg(b.title)::text[], array[]::text[]) as book_list
FROM (
	SELECT id, name, bio
    FROM authors
    WHERE name = $1
    LIMIT 1 
) a
LEFT JOIN author_book ab ON a.id = ab.author_id
LEFT JOIN books b ON ab.book_id = b.id
GROUP BY a.id
LIMIT 1
`

type GetAuthorRow struct {
	ID       int64
	Name     string
	Bio      sql.NullString
	BookList interface{}
}

func (q *Queries) GetAuthor(ctx context.Context, name string) (GetAuthorRow, error) {
	row := q.db.QueryRowContext(ctx, getAuthor, name)
	var i GetAuthorRow
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.Bio,
		&i.BookList,
	)
	return i, err
}

For the calculated book_list column I wanted to get []sql.NullString, but it is giving interface{}. This behavior is stated on
#185 which is still an open issue.
It sounds a very hacky solution, but it can be solved by allowing type override of calculated columns as well. Something like this:

        overrides:
        - column: "book_list"
          go_type:
              import: "database/sql"
              type: "NullString"

And I assume sqlc would figure out this is an array type from the array_agg() function name.

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[mxey]

Ignoring the issue of potentially having nulls in arrays, you can make sqlc use a given type by doing a type cast in your SQL:

coalesce(array_agg(b.title)::text[], array[]::text[])::text[] as book_list

this will give you a []string which according to #185 it should not do, because arrays can have NULLs.

However, it seems your book titles can never be null anyway, because you have a not null on books.title, so that shouldn't be a problem. (if there is some instance where you need to filter out NULLs from your aggregate, you can probably use the FILTER keyword with your aggregate)

btw I think array_agg() will be an empty array if there are no books, so it will never be NULL and you don't need the COALESCE:

array_agg(b.title)::text[]

[sobocinski]

Hi, I'm having similar issue - override interval to pgtype.Interval,

my query looks like this:

-- name: GetResultsRow :many
WITH best_lap_times AS (
    SELECT
        ss.best_lap_time,
        ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY ss.best_lap_time ASC) AS lap_rank
    FROM session_statistics AS ss
    JOIN user u ON u.id = ss.user_id
    WHERE s.layout_id =$1
)
SELECT
    best_lap_time
FROM best_lap_times
WHERE lap_rank = 1
ORDER BY best_lap_time ASC;

ss.best_lap_time in PostgreSQL have type interval, but SQLC generates struct like this:

type GetResultsRow struct {
	BestLapTime   int64          `json:"best_lap_time"`
}

sqlc.yaml looks like this,

version: "2"
overrides:
  go:
    overrides:
      - db_type: "interval"
        engine: "postgresql"
        go_type:
          import: "github.com/jackc/pgtype"
          package: "pgtype"
          type: "Interval"

and I have error during run code: can't scan into dest[4]: unable to assign to *int64.
Am I doing something wrong that the library doesn't recognize the right type?

[kevboh]

For what it's worth, I'd like this as well: the ability to arbitrarily override the types of columns in queries. I frequently construct ad-hoc jsonb results to efficiently query for sets of data, and would love to shift the unmarshalling of these results deeper into the stack.