Override columns returned by functions

[mohdrasbi]

What do you want to change?

Ability to override selected columns from postgres function to Go structs.

Current implementation

Let's say we have the following table:

create table users (
    id bigserial primary key,
    name text not null,
    additional_info jsonb not null
);

And let's say additional_info is mapped to this Go struct:

type UserInfo struct {
    email string `json:"email"`
    phone string `json:"phone"`
}

To override the type additional_info, we would do the following:

#file: sqlc.yaml
version: "2"
sql:
  - schema: "./schema"
    queries: "./queries"
    engine: "postgresql"
    gen:
      go:
        out: "./gen"
        package: "gen"
        emit_json_tags: true
        sql_package: "pgx/v5"
        emit_empty_slices: true
        overrides:
          - nullable: false
            column: public.users.additional_info
            go_type: UserInfo

Then when we select the column, it will be successfully mapped to the struct.

Scenario

But what if I have a postgres function that looks like this:

create or replace function user_get(_user_id bigint)
returns table (
  id bigint,
  name text,
  info jsonb
  ) as $$
begin
  return query
  select id, name, additional_info
  from users where id = _user_id;
end;
$$ language plpgsql;

And the query looks like this:

-- name: GetUser :one
select id::bigint, name::text, info::jsonb from user_get(@user_id::bigint);

The generated Go code will be:

type GetUserRow struct {
    ID        int64              `json:"id"`
    Name       string        `json:"name"`
    Info      []byte             `json:"info"`
}

Issue

Is it possible to map the info column that is returned from the function to UserInfo struct either by specifying the type in the query itself or in sqlc.yaml config file?

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[anazcodes]

Thanks for posting this issue. Your question helped me to figure out my issue.

[Hades32]

The same issue seems to happen for simple generated columns like this

SELECT jsonb_object_agg(i.id, true) as online_integrations
FROM integrations i
WHERE ...

then this mapping does not apply it seems

version: 2
sql:
  - engine: "postgresql"
    schema: "schema.sql"
    queries: "./queries/"
    gen:
      go:
        sql_package: "pgx/v5"
        out: "query"
        package: "query"
        overrides:
          - column: "*.online_integrations"
            go_type: "github.com/my/test/query.OnlineIntegrationsMap"

with the type just being

type OnlineIntegrationsMap map[string]bool

also the docs only mention the *.column notation in some example, but the docs for the overrides says it has to be table.column 🤷

[anazcodes]

    overrides:
        - db_type: "jsonb"
          go_type:
            import: "github.com/specify/the/package/which/holds/the/type"
            type: "OnlineIntegrationsMap"

The configuration may look like this, try this. If issue still persist share the sqlc playground link with your sql script and configurations.

[Hades32]

But wouldn't this config change the type for ALL jsonb columns?

[anazcodes]

Yes I think so, try to use Postgres Domain for this particular field and specify the name of the Domain in the configuration with the type.

version: 2
sql:
  - engine: postgresql
    schema: schema.sql
    queries: ./queries/
    gen:
      go:
        sql_package: pgx/v5
        out: query
        package: query
        overrides:
          - db_type: my_custom_json
            go_type:
              import: github.com/specify/the/package/which/holds/the/type
              type: OnlineIntegrationsMap

CREATE DOMAIN my_custom_json AS jsonb; SELECT jsonb_object_agg(i.id, true)::my_custom_json AS online_integrations FROM integrations ;

Try this I am not sure.