search

sqlc-dev / sqlc

Generate type-safe code from SQL
https://sqlc.dev
MIT License
13.42k stars 803 forks source link

Assign types to json aggregations #2788

Open boyswan opened 1 year ago

boyswan commented 1 year ago

What do you want to change?

AFAIK sqlc cannot assign types to json aggregations.

For example:

select 
  ...
  json_build_object(
    'id', s.id,
    'description', s.description
  ) as Show
from ...

Show will be exported as []bytes. I am able to get around this by skipping sqlc and using pgx:

v, err := pgx.CollectRows(rows, pgx.RowToAddrOfStructByName[types.PersonWithShow])

However seeing that sqlc can assign types to jsonb at a column level via overrides, I feel it would be very useful to be able to assign types to json at a query level.

What I would like to be able to do is:

  json_build_object(
    'id', s.id,
    'description', s.description
  )::Show as Show

I went down a rabbit hole trying to add custom pg types and setting db_type as an attempt for this to work in sqlc's current state, but gave up as I wasn't convinced this was the right direction.

Is this something that is possible in sqlc?

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

kyleconroy commented 1 year ago

Tracking JSON unmarshaling of custom structs here: https://github.com/sqlc-dev/sqlc/issues/2761 and composite type support here #2760.

hariangr commented 11 months ago

Any update on this?

asineth0 commented 6 months ago

I would really like this.