Return type of Postgres functions converting into interface{} instead of Struct.

Version

1.27.0

What happened?

Sqlc not binding Postgres function's table type returning to a struct, it is directly binding into an interface{} type as an array.

Relevant log output

[5 2024-10-07 17:42:57.801246 +0530 IST 2024-10-07 17:42:57.801246 +0530 IST]

Database schema

CREATE TABLE IF NOT EXISTS public.users
(
    id bigserial NOT NULL,
    phone text ,
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    CONSTRAINT users_pkey PRIMARY KEY (id)
)

CREATE OR REPLACE FUNCTION public.fn_authrepo_create_user(
    var_phone text,
    var_created_at timestamp with time zone,
    var_updated_at timestamp with time zone)
    RETURNS TABLE(user_id BIGINT, created_at timestamp with time zone, updated_at timestamp with time zone)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    v_user_id BIGINT;
    v_created_at TIMESTAMPTZ;
    v_updated_at TIMESTAMPTZ;
BEGIN
    INSERT INTO users (phone, created_at, updated_at)
    VALUES (var_phone, var_created_at, var_updated_at)
    RETURNING users.id, users.created_at, users.updated_at
    INTO v_user_id, v_created_at, v_updated_at;

    RETURN QUERY SELECT v_user_id, v_created_at, v_updated_at;
END;
$BODY$;

SQL queries

-- name: CreateUser :one
SELECT * FROM fn_authrepo_create_user($1, $2, $3);

Configuration

version: "2"
cloud:
  project: "<PROJECT_ID>"
sql:
- schema: "migrations/authdb/schema/*.sql"
  queries: "migrations/authdb/queries/*.sql"
  engine: "postgresql"
  gen:
    go: 
      package: "authdb"
      out: "internal/repository/authrepo/authdb"
      sql_package: "pgx/v5"
      emit_sql_as_comment: true
      emit_interface: true
      emit_prepared_queries: true

Playground URL

https://play.sqlc.dev/p/149e4929dbc625971466f68f4c1178337f2d4fb6e6168104aa2a007ff8f2f807

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

Figured out the way to do it

Figured out the way to do it

@anazcodes Can you please explain how you made it? For now I have to write custom functions that return table into a separated .go file instead of using auto-generated one.

@RunicBean

Figured out the way to do it

@anazcodes Can you please explain how you made it? For now I have to write custom functions that return table into a separated .go file instead of using auto-generated one.

@RunicBean

queries.sql

Define the returning fields like this with appropriate type casting.

-- name: CreateUser :one
SELECT user_id::BIGINT, phone::TEXT, created_at::TIMESTAMPTZ, updated_at::TIMESTAMPTZ FROM fn_authdb_create_user($1, $2, $3);

schema.sql

Define the procedure in schema file.

CREATE OR REPLACE FUNCTION public.fn_authdb_create_user(
    var_phone text,
    var_created_at timestamp with time zone,
    var_updated_at timestamp with time zone)
    RETURNS TABLE(user_id BIGINT, phone text, created_at timestamp with time zone, updated_at timestamp with time zone)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    v_user_id BIGINT;
    v_phone TEXT;
    v_created_at TIMESTAMPTZ;
    v_updated_at TIMESTAMPTZ;
BEGIN
    INSERT INTO users (phone, created_at, updated_at)
    VALUES (var_phone, var_created_at, var_updated_at)
    RETURNING users.id, users.created_at, users.updated_at
    INTO v_user_id, v_created_at, v_updated_at;

    RETURN QUERY SELECT v_user_id, v_phone, v_created_at, v_updated_at;
END;
$BODY$;

Note: This does not work for procedures. Only works for PostgreSQL functions.

Hope this helps.

@anazcodes this solution worked great for me, until I needed the function to return a nullable parameter.

The return model generated by sqlc always uses int32 instead of the nullable PgInt or a *int32.
And as soon as the procedure returns a null value, the app panics by trying to scan null into int32.

Any ideas how to adjust the query so that sqlc understands the return value is nullable?

@irreal

Hi @irreal, you can use this configuration for reference.

Configuration

- schema: "migrations/productdb/schema/*.up.sql"
  queries: "migrations/productdb/queries/*.sql"
  engine: "postgresql"
  gen:
    go:
      package: "productdb"
      out: "internal/repository/productrepo/productdb"
      sql_package: "pgx/v5"
      emit_interface: true
      # emit_pointers_for_null_types: true  // this also may help
      emit_prepared_queries: true
      # omit_unused_structs: true
      emit_sql_as_comment: true
      overrides:
        - db_type: "text"
          go_type:
            import: "github.com/jackc/pgx/v5/pgtype"
            type: "Text"

Try out with this, you may get corresponding pgtype for DB types, mention overrides with it.

Here is what I achieved through this way (Auto Gen).

const getProducts = `-- name: GetProducts :many
select id::bigint, name::text, description::text, price::numeric, created_at::timestamp with time zone, updated_at::timestamp with time zone
from public.fn_productrepo_get_products()
`

type GetProductsRow struct {
	ID          int64
	Name        pgtype.Text
	Description pgtype.Text
	Price       pgtype.Numeric
	CreatedAt   pgtype.Timestamptz
	UpdatedAt   pgtype.Timestamptz
}

// GetProducts
//
//	select id::bigint, name::text, description::text, price::numeric, created_at::timestamp with time zone, updated_at::timestamp with time zone
//	from public.fn_productrepo_get_products()
func (q *Queries) GetProducts(ctx context.Context) ([]GetProductsRow, error) {
	rows, err := q.db.Query(ctx, getProducts)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []GetProductsRow
	for rows.Next() {
		var i GetProductsRow
		if err := rows.Scan(
			&i.ID,
			&i.Name,
			&i.Description,
			&i.Price,
			&i.CreatedAt,
			&i.UpdatedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}

Hi @anazcodes thank you so much!

emit_pointers_for_null_types: true did not help, I think sqlc is just failing to understand it's nullable.

however, I was able to override the type in the config:

          - db_type: "int4"
            go_type:
              import: "github.com/jackc/pgx/v5/pgtype"
              type: "Int4"

this worked great.

I then did select id::int4 in the query.

for some reason, doing select id::integer and overriding "integer" in sqlc.yaml did not work.
but int4 is good.

It of course does the override even in other places, where the column is guaranteed not null, but that is acceptable to me.

thanks!

Navigation Menu

Repository navigation

Return type of Postgres functions converting into interface{} instead of Struct. #3638

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

queries.sql

schema.sql

Configuration

Here is what I achieved through this way (Auto Gen).

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Return type of Postgres functions converting into interface{} instead of Struct. #3638

Description

Version

What happened?

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Activity

anazrasheed commented on Oct 7, 2024

RunicBean commented on Dec 7, 2024

anazrasheed commented on Jan 2, 2025

queries.sql

schema.sql

irreal commented on May 19, 2025

anazrasheed commented on May 20, 2025

Configuration

Here is what I achieved through this way (Auto Gen).

irreal commented on May 20, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions