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

[anazibinurasheed]

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