Arguments to SQL functions are nullable by default

While working on a solution for #364 which is based on SQL functions, I hit the following problem.

Arguments for SQL functions in PostgreSQL are nullable by default. With CALLED ON NULL INPUT, this can even be explicitly stated, but this is ignored by sqlc.

Given the following definition for PostgreSQL:

CREATE TABLE foo (
            id     INTEGER,
            bar    varchar(100)
);

INSERT INTO foo VALUES (null, 'foo'), (1, 'bar');

CREATE OR REPLACE FUNCTION select1(_id INTEGER)
  RETURNS SETOF foo as
$func$
BEGIN
  IF _id IS NULL THEN
    RETURN QUERY EXECUTE 'select * from foo where id IS NULL';
  ELSE
    RETURN QUERY EXECUTE FORMAT('select * from foo where id = %L', _id);
  END IF;
END
$func$ LANGUAGE plpgsql CALLED ON NULL INPUT;

the following SQL calls are valid:

select select1(null);
select select1(1);

With the query definition:

-- name: GetSelect1 :many
SELECT select1($1);

the following Go method signature is generated:

func (q *Queries) GetSelect1(ctx context.Context, ID int32) ([]interface{}, error) {

With this method signature it is not possible to pass nil (or SQL null) as argument for ID.

Update: fix typo

I looked into this in a more detail and it looks like the underlying github.com/lfittl/pg_query_go does not return the correct information.

Given the following example:

package main

import (
	"fmt"

	pg_query "github.com/lfittl/pg_query_go"
)

func main() {
	tree, err := pg_query.ParseToJSON(`CREATE OR REPLACE FUNCTION select1(_id INTEGER)
  RETURNS SETOF foo as
$func$
BEGIN
  IF _id IS NULL THEN
    RETURN QUERY EXECUTE 'select * from foo where id IS NULL';
  ELSE
    RETURN QUERY EXECUTE FORMAT('select * from foo where id = %L', _id);
  END IF;
END
$func$ LANGUAGE plpgsql CALLED ON NULL INPUT;`)
	if err != nil {
		panic(err)
	}
	fmt.Printf("%s\n", tree)
}

I get this output:

[
  {
    "RawStmt": {
      "stmt": {
        "CreateFunctionStmt": {
          "replace": true,
          "funcname": [
            {
              "String": {
                "str": "select1"
              }
            }
          ],
          "parameters": [
            {
              "FunctionParameter": {
                "name": "_id",
                "argType": {
                  "TypeName": {
                    "names": [
                      {
                        "String": {
                          "str": "pg_catalog"
                        }
                      },
                      {
                        "String": {
                          "str": "int4"
                        }
                      }
                    ],
                    "typemod": -1,
                    "location": 39
                  }
                },
                "mode": 105
              }
            }
          ],
          "returnType": {
            "TypeName": {
              "names": [
                {
                  "String": {
                    "str": "foo"
                  }
                }
              ],
              "setof": true,
              "typemod": -1,
              "location": 64
            }
          },
          "options": [
            {
              "DefElem": {
                "defname": "as",
                "arg": [
                  {
                    "String": {
                      "str": "\nBEGIN\n  IF _id IS NULL THEN\n    RETURN QUERY EXECUTE 'select * from foo where id IS NULL';\n  ELSE\n    RETURN QUERY EXECUTE FORMAT('select * from foo where id = %L', _id);\n  END IF;\nEND\n"
                    }
                  }
                ],
                "defaction": 0,
                "location": 68
              }
            },
            {
              "DefElem": {
                "defname": "language",
                "arg": {
                  "String": {
                    "str": "plpgsql"
                  }
                },
                "defaction": 0,
                "location": 270
              }
            },
            {
              "DefElem": {
                "defname": "strict",
                "arg": {
                  "Integer": {
                    "ival": 0
                  }
                },
                "defaction": 0,
                "location": 287
              }
            }
          ]
        }
      },
      "stmt_len": 307
    }
  }
]

The interesting part is the last DefElem, which is strict, but we would expect it to be called on null input.

In order to allow nullable inputs for sql functions, the this line (https://github.com/kyleconroy/sqlc/blob/master/internal/compiler/resolve.go#L267) would needed to be changed returning false instead of true.
But this change obviously breaks several unit tests as well as it would break the code for all existing users. So the question is, what is the best way to fix this. Either some sort of configuration or the above mentioned breaking change.

I think the latest release v1.14.0 contains #1536, which provides the option to specify nullable arguments with sqlc.narg().

For your use case, it might work to use:

SELECT select1(sqlc.narg(_id)::integer);

which would generate sql.NullInt32 as the Go type for _id.

I believe that both sqlc.narg and #2800 solve this issue. We'll also want to revisit the default nullability at some point in the future, but it's a backwards incompatible change right now.

Navigation Menu

Repository navigation

Arguments to SQL functions are nullable by default #940

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Arguments to SQL functions are nullable by default #940

Description

Activity

breml commented on Mar 17, 2021

breml commented on Mar 17, 2021

Yiwen-Gao commented on Jul 10, 2022

kyleconroy commented on Oct 16, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions