cockroachdb / cockroachdb/cockroach

sql: support named arguments for routines

Open
#122,264 0 comments 0 reactions 0 assignees View on GitHub
A-sql-routine C-enhancement docs-done docs-known-limitation O-qa T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

In addition to the usual positional notation, postgres allows a routine to be invoked with named arguments:
```
postgres=# create function foo (a int, b int) returns int language sql as $$ select a; $$;
CREATE FUNCTION

-- Positional args
postgres=# select foo(1, 2);
foo
-----
1
(1 row)

-- Named args in usual order
postgres=# select foo(a => 1, b => 2);
foo
-----
1
(1 row)

-- Named args in reverse order
postgres=# select foo(b => 1, a => 2);
foo
-----
2
(1 row)
```
It's also possible to use `:=` instead of `=>`:
```
postgres=# select foo(b := 1, a := 2);
foo
-----
2
(1 row)
```
Finally, named and positional notation can be used together, as long as named arguments follow all positional arguments:
```
postgres=# create function foo (a int, b int default 200, c int default 300) returns int language sql as $$ select b; $$;
CREATE FUNCTION

postgres=# select foo(1, 2);
foo
-----
2
(1 row)

postgres=# select foo(1, c => 2);
foo
-----
200
(1 row)

postgres=# select foo(1, c => 2, b => -1);
foo
-----
-1
(1 row)
```

PG docs: https://www.postgresql.org/docs/current/sql-syntax-calling-funcs.html#SQL-SYNTAX-CALLING-FUNCS-NAMED

Jira issue: CRDB-37780

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.