-
Notifications
You must be signed in to change notification settings - Fork 802
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Arguments to SQL functions are nullable by default #940
Comments
I looked into this in a more detail and it looks like the underlying 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 |
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 |
I believe that both |
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 bysqlc
.Given the following definition for PostgreSQL:
the following SQL calls are valid:
With the query definition:
the following Go method signature is generated:
With this method signature it is not possible to pass
nil
(or SQLnull
) as argument forID
.Update: fix typo
The text was updated successfully, but these errors were encountered: