
# Database functions

`@Procedure('name')` on a repository method calls a database function by name. The method's parameters are its named arguments, and what the function returns is read as the result of a [`@Query`](query.md) is.

## Why

Some work belongs in the database: several writes that must happen together, a query the planner does better with than a client, a rule every client must follow. A function is also the way to do several writes atomically on a store without transactions, such as a REST API. Calling one by hand means its SQL, the argument order and a row mapper; `@Procedure` is the name and the method's signature.

## Example

```dart
abstract interface class Accounts
    implements CrudRepository<Account, int> {
  // select * from "transfer"("from_id" => ?, "to_id" => ?, "amount" => ?)
  @Procedure('transfer')
  Future<void> transfer(int fromId, int toId, int amount);

  @Procedure('accounts_over')
  Future<List<Account>> over(int balance);
}
```

```sql
create function accounts_over(balance int) returns setof accounts
language sql stable as $$ select * from accounts where accounts.balance > $1 $$;
```

## Arguments

- Each parameter of the method is one argument of the function, passed **by name**.
- The name is the parameter's name in snake case, as columns are named: `accountId` is `account_id`. The naming strategy of the mapping names them.
- Values are converted as column values are: an enum is its `name`.
- A list is one array argument.
- `@Procedure('schema.name')` calls a function outside the search path.

## Results

What the function returns is read as the rows of a `@Query` are:

| The method returns | From |
|---|---|
| `Future<List<Account>>`, `Future<Set<Account>>`, `Stream<Account>` | The rows: the repository's entity, or a class of the application built from the columns by name. A `Stream` calls the function when it is listened to. |
| `Future<List<int>>`, `Future<Set<String>>` | A single column of each row. |
| `Future<Account?>`, `Future<int>` | One value. No row is `null` for a nullable type, else an error. |
| `Future<List<Map<String, Object?>>>` | The rows as maps. |
| `Future<void>` | Nothing: what the function returns is ignored. |

The build checks the method, and a mistake is one of the repository's problems:

- a `Page` or a `Slice` cannot be returned: a function is not a query to count;
- there is no `Pageable` or `Sort` parameter: every parameter of the method is an argument of the function;
- `@Procedure` does not go together with `@Query`, `@Modifying` or `@Lock`;
- the name of the function is not empty.

A method with `@Procedure` is never read as a derived query, whatever its name.

## Stores

The store of the repository must have database functions (see [Stores and their capabilities](stores.md)). On a store without them the repository fails when it is created, and at build time when the build knows the store.

| Store | The call |
|---|---|
| SQL on PostgreSQL | `select * from "name"("argument" => ?)`, in the current transaction of the caller: a rollback undoes what the function wrote. |
| SQL on SQLite | no functions |
| in memory | no functions |
| `supabase` ([spring_boot_starter_supabase](../spring-boot-starter-supabase/data.md)) | `POST /rest/v1/rpc/name` (`rpc` is the REST API's own path), as the current user; one request, so atomic by itself. |

## Errors

Failures are [`DataAccessException`s](errors.md), as for every repository method:

- a function that does not exist is a `BadSqlGrammarException` on PostgreSQL;
- a privilege the caller lacks, or an exception the function raises with the state `42501`, is a `PermissionDeniedDataAccessException`;
- the wrong number of arguments is an `InvalidDataAccessApiUsageException`.

## What the generator writes

For each `@Procedure` method the generator writes a `ProcedureMethod` constant (the method's name, its annotation, its parameters, the shape and the element of its result) and a call of the repository's operations:

```dart
// For: @Procedure('accounts_over')
//      Future<List<Account>> over(int balance);
static const _over = ProcedureMethod(
  r'over',
  Procedure(r'accounts_over'),
  parameters: [r'balance'],
  element: ClassType<Account>(),
);

@override
Future<List<Account>> over(int balance) =>
    this.operations.procedure<List<Account>>(_over, [balance]);
```

The constants are handed to the repository's operations as `functions:`, and each method as a `StoreRequirement` of `functions`.

## The run time

Under the generated code there are two layers a library can use directly.

The store: `FunctionDataStore.callFunction(name, arguments)` calls a function and gives its rows as maps of column names to values.

```dart
final rows = await store.callFunction('restock', {
  'sku': 'A-1',
  'quantity': 5,
});
```

The operations of a repository: a `ProcedureMethod` describes one `@Procedure` method (its name, its annotation, its parameters, the shape and the element of its result), and `RepositoryOperations.procedure<R>` and `procedureStream<E>` call it with the arguments in the order of the parameters:

```dart
const named = ProcedureMethod(
  'named',
  Procedure('authors_named'),
  parameters: ['prefix'],
  element: ClassType<Author>(),
);

final authors = RepositoryFactory(
  mappingContext: mappingContext,
  converter: converter,
  stores: [store],
).operations<Author, int>(
  'Authors',
  annotations: const [UseStore('jdbc')],
  functions: const [named],
);

final found = await authors.procedure<List<Author>>(named, ['He']);
```

## In Spring (Java)

`@Procedure("name")` of Spring Data JPA; Spring Data JDBC has none. The store's call is `SimpleJdbcCall.withFunctionName(name).execute(arguments)`.

## See also

- [Stores and their capabilities](stores.md)
- [@Query](query.md)
- [Transactions](transactions.md)
- [PostgreSQL](postgresql.md)
- [The generator and data](../spring-core-dart-generator/data.md)
