# Procedures

> Write a procedure for work that takes more than one query, run it with arguments, and add it to a task.

A view is one query. A procedure is a named block of SQL that can do several things in order: create a table, clear a slice of it, refill it from your landing tables, commit between steps. You run it with arguments, by hand or from a [task](https://docs.sanda-os.com.au/modelling/tasks).

Use a procedure when a view cannot express the work, for example to keep an incrementally updated reporting table, or to prepare data in steps. Only owners and admins can create, run or drop one.

## What a procedure may do

A procedure runs as the warehouse's builder role. That role can:

- read every table in `raw` and `derived`, and the tables in `mapping`;
- create, change and drop objects in `derived`, and read and write the tables it makes there;
- commit between steps, when the body is `plpgsql`.

It cannot write anywhere else, and it cannot touch a published `mapping` view, sanda's bookkeeping, or another database, whatever the body says. sanda does not scan a procedure body for what it might do, because no scanner could vouch for arbitrary code. The role's permissions are the boundary.

What a procedure can do is spend compute. That is why creating and running one needs an owner or admin, and why every run is recorded with its duration and cost.

## Create a procedure

:::steps
1. **Open the Procedures tab.** In **Data · Modelling**, choose **Procedures**, then **Write SQL** under **Or start your way**. Or press **Describe the procedure to cherry** and let cherry draft it (examples include **Prepare a daily revenue summary**).
2. **Name it.** Lowercase letters, digits and underscores, starting with a letter, at most 63 characters. It becomes `derived.<name>`.
3. **Declare the arguments.** In **Arguments**, write `name type, name type`, for example `since date, region text`. Leave it empty for none. Allowed types are `text`, `integer`, `bigint`, `numeric`, `boolean`, `date`, `timestamptz`, `timestamp`, `interval`, `jsonb` and `uuid`. A procedure takes at most 16 arguments.
4. **Choose the language.** `plpgsql` is a block with variables, loops and commits. `sql` is statements one after another.
5. **Write the body.** Up to 20,000 characters. The body cannot contain the text `$becca_body$`, which sanda uses to wrap it.
6. **Create it.** Press **Create procedure**.
:::

```sql title="Body of a procedure with the argument since date, language plpgsql"
begin
  create table if not exists derived.daily_sales (
    sale_date date primary key,
    revenue numeric not null
  );

  delete from derived.daily_sales where sale_date >= since;

  insert into derived.daily_sales (sale_date, revenue)
  select s.sale_date, sum(s.revenue)
  from raw.csv__sales s
  where s.sale_date is not null
    and s.sale_date >= since
  group by s.sale_date;

  commit;
end
```

This one clears everything from a date onward, then rebuilds it, so it is safe to run twice. Table and column names depend on your data. Find them in the [Explorer](https://docs.sanda-os.com.au/warehouse/explorer).

In the `sql` language the body is just the statements, one after another. PostgreSQL checks a `sql` body when you create the procedure, so this version needs `derived.daily_sales` to exist already:

```sql title="The same idea in the sql language, argument since date"
delete from derived.daily_sales where sale_date >= since;
insert into derived.daily_sales (sale_date, revenue)
select s.sale_date, sum(s.revenue)
from raw.csv__sales s
where s.sale_date is not null and s.sale_date >= since
group by s.sale_date;
```

:::note
Tables a procedure creates are ordinary tables in `derived`. sanda does not record them as modelled objects, so they have no version history, and the Explorer marks them **Made outside sanda**. To use one on the semantic fluid, add it as you would any table. See [Tables on the fluid](https://docs.sanda-os.com.au/semantic-fluid/tables).
:::

## Preview

A procedure has nothing to preview, so the builder shows **Source preview**: a read-only look at the source data, when a query for it is provided, and a **Steps** tab listing the steps cherry planned when it drafted the procedure. The procedure has not run, and those rows are not a simulation of its output.

## Run a procedure

Select the procedure and, if it takes arguments, type them in the box beside **Run**, separated by commas and in the order they were declared. An empty argument is passed as null. Press **Run**.

When it finishes, sanda says how long it ran and what it was billed as, for example **Ran in 4.2 s, billed as 4.2 s of compute.** A run started from the page has 55 seconds. If it takes longer, it is stopped, and sanda suggests adding it to a task, which has up to 14 minutes.

If the arguments do not fit, sanda says how many it takes and what they are called.

## Keep it running

Press **Schedule task** on the object, or use **Save & schedule** when you save it. A task calls the procedure with the arguments you give, on a schedule or after a sync. See [Tasks](https://docs.sanda-os.com.au/modelling/tasks).

## Redefine and drop

**Redefine** replaces the procedure. A name is one object, so changing the arguments replaces the procedure rather than adding a second version with a different signature. Each definition is a version, and the old ones stay in the history.

**Drop** asks you to confirm. The procedure goes and its definitions stay in the history. A task that calls it skips that step. Tables the procedure built are not dropped.

## History

The **History** tab shows every version with the statement sanda ran, every run with its duration, cost and who started it, and the audit records. It is the same tab as in the Explorer.

:::links
- [Tasks](https://docs.sanda-os.com.au/modelling/tasks): Run a procedure on a schedule.
- [Views and modelled tables](https://docs.sanda-os.com.au/modelling/views): For work a single query can do.
- [SQL reference](https://docs.sanda-os.com.au/reference/sql): Dialect, limits and worked examples.
:::