sqlc should treat PostgreSQL function return values as nullable (e.g. *string) to avoid scan errors

[aaffriya]

📌 Problem

When using sqlc v1.30.0 with a PostgreSQL function that returns a nullable scalar type like TEXT, sqlc generates non-pointer types (e.g. string), which causes runtime errors when the function returns NULL.

---

💥 Example

Given the function:

CREATE FUNCTION mark_email_as_verified(p_user_id UUID)
RETURNS TEXT AS $$
BEGIN
    RETURN NULL; -- success
END;
$$ LANGUAGE plpgsql;

And the query:

-- name: MarkEmailAsVerified :one
SELECT mark_email_as_verified($1) AS result;

sqlc generates:

func (q *Queries) MarkEmailAsVerified(ctx context.Context, pUserID uuid.UUID) (string, error)

If the function returns NULL, this causes:

can't scan into dest[0] (col: result): cannot scan NULL into *string

Or similar runtime panic from Go's database/sql or pgx drivers.

---

✅ Expected Behavior

The generated Go function should be:

func (q *Queries) MarkEmailAsVerified(ctx context.Context, pUserID uuid.UUID) (*string, error)

Since PostgreSQL functions returning TEXT, UUID, INT, etc. can return NULL, sqlc should always generate pointer types for these cases.

---

✅ Workaround

Set the following in sqlc.yaml (v1.30.0 supports this):

version: "2"
sql:
  - engine: "postgresql"
    schema: "schema.sql"
    queries: "queries.sql"
    emit_pointers_for_null_types: true

This ensures nullable scalar types like TEXT are correctly mapped to Go pointers (e.g. *string), avoiding scan errors.

---

💡 Suggested Improvements

1. Default Behavior:

   • Treat function return values as nullable unless explicitly known to be non-null.
   • Generate Go pointer types (*string, *uuid.UUID, etc.) for any scalar-returning function.

2. Optional per-query override, e.g.:

   -- name: MarkEmailAsVerified :one
   -- nullable: true
   SELECT mark_email_as_verified($1);
   

3. Better error message:

   • When the generated code causes a scan error due to NULL, sqlc should suggest enabling emit_pointers_for_null_types.

---

📦 Version Info

• sqlc version: v1.30.0
• SQL engine: PostgreSQL
• Go driver: pgx (via pgx/v5)

---

✅ Summary

This bug leads to runtime error that are non-obvious and difficult to trace, especially for newer users.

Since PostgreSQL functions can always return NULL, sqlc should default to generating nullable Go types (*T) or offer a safer default behavior to avoid silent breakage.

---

[No3371]

After digging a bit, it seems like internal/sql/catalog/func.go -> ReturnTypeNullable of Function is never set except for the some pre-defined standard functions FOR OTHER databases. That explains why all custom function is considered returning non-null value.

[No3371]

The behavior can be overridden by setting ReturnTypeNullable at internal/sql/catalog/func.go:66. This change is unlikely to be safely compatible with all engines.