# PL/pgSQL Conventions — Starter Rules for Claude Code

> Drop this file into your repository as `.claude/rules/sql.md`. Claude Code loads it
> as a project instruction every session and generates SQL in this house style.
>
> Distilled from a production ETL project; project-specific internals removed.
> `:schema_app_name` is a placeholder — replace it with your schema name or keep it
> as a psql variable. Treat every rule as **Rule → Reason → Example**: the *reason*
> lets the agent generalize to cases this file doesn't spell out.

## Naming

### Prefixes

| Object type      | Prefix | Example              |
|------------------|--------|----------------------|
| Stored Procedure | `sp_`  | `sp_upd_project`     |
| Stored Function  | `fn_`  | `fn_is_null_or_empty`|
| Trigger Function | `tf_`  | `tf_project`         |
| Trigger          | `tr_`  | `tr_iud_project`     |
| View             | `vw_`  | `vw_active_projects` |

### Verb codes (procedures)

- `ins` = insert, `upd` = update, `del` = delete
- `get` = select, `exe` = execute
- `dup` = duplicate (copy a row to a new row with a new surrogate key)

Procedure name = `sp_<verb>_<entity>`. Trigger name = `tr_<type>_<entity>`, where `<type>`
is `i` / `u` / `d` or the combination `iud`.

### Cross-cutting rules

- **Always `snake_case`.**
- **Always singular table names** (`user`, `project`, `task` — never `users`). FK columns
  follow: `user_id`, not `users_id`.
- **Timestamp columns end in `_on`**, never `_at`: `created_on`, `modified_on`.
- **Every table gets a surrogate primary key** `id bigserial NOT NULL` with
  `CONSTRAINT pk_<table> PRIMARY KEY (id)`. Natural keys become `UNIQUE` constraints,
  not the PK.
- **Parameters are prefixed `p_`**, local variables `l_`. The mode keyword
  (`IN` / `OUT` / `INOUT`) carries the direction — do not encode it in the name
  (`p_result`, not `p_out_result`).
- **Never hard-code the schema name** — use a parameter or a psql variable
  (`:schema_app_name`).
- **Reserved-keyword tables:** prefer `account` over `user` — Postgres treats `USER`
  as a synonym for `CURRENT_USER`, so a bare `FROM user` parses as a function call.

> Some of these are hard readability/portability arguments (`snake_case`, `p_`/`l_`,
> non-hard-coded schema); others (`_on` suffix, always-surrogate-PK, `account` naming)
> are house style. The decisive point is not *which* form, but that it is applied
> **consistently** — that is what turns `_on`, `p_` or `T0n` into reliable search patterns.

## Body structure: collapsible blocks

A procedure/function body is split into clearly delimited blocks, each introduced by an
80-dash banner and — except the first — wrapped in its own `BEGIN ... END;` sub-block so
it folds in the editor. The standard blocks are
`Get name → Initialize → Check parameter → Workload`, but the structure is **not limited
to a fixed count** — add a block per logical step as needed. **Checks run before mutations.**

```sql
BEGIN
   -- --------------------------------------------------------------------------------
   -- Get name of procedure
   -- --------------------------------------------------------------------------------
   SET LOCAL lc_messages TO 'C';   -- optional: force English server messages
   GET DIAGNOSTICS l_context = PG_CONTEXT;
   l_component := substring(l_context from 'function (.*?)\(');

   -- --------------------------------------------------------------------------------
   -- Initialize variables
   -- --------------------------------------------------------------------------------
   BEGIN
      NULL;   -- hard-coded start values or SELECT ... INTO
   END;

   -- --------------------------------------------------------------------------------
   -- Check parameter
   -- --------------------------------------------------------------------------------
   BEGIN
      NULL;   -- input / permission checks, BEFORE any mutation
   END;

   -- --------------------------------------------------------------------------------
   -- Workload
   -- --------------------------------------------------------------------------------
   BEGIN
      NULL;   -- lookups, mutations, RETURN
   END;
END;
```

An empty `BEGIN END;` is a syntax error in PL/pgSQL — use at least `NULL;`.
The `PG_CONTEXT` trick is convenience, not a must: drop it or set the component name as a
constant if you don't need the trace.

## Arguments as variables (not inline)

Assign each value to a variable first; the call (or `RAISE`) receives only variables.
This separates the *what* (the value) from the *how* (the call) and keeps the call line
readable.

**Error messages** via `format($$...$$, ...)`:

- Dollar-quoting (`$$...$$`) as the template delimiter — single quotes around text values
  stay single (`'%2$s'`).
- Indexed placeholders only (`%1$s`, `%2$s`), never bare `%`. Indexed placeholders can
  reference the same argument more than once without passing it again.
- Text values in single quotes (`'%2$s'`), numeric values without. The component prefix
  stays unquoted.
- Message and error code go into variables first; keep the original ERRCODE, never invent one.

```sql
l_error_message := format($$%1$s: actor='%2$s' may not edit project id=%3$s$$, l_component, p_actor_email, l_project_id);
l_error_code    := 'insufficient_privilege';

RAISE EXCEPTION USING MESSAGE = l_error_message, ERRCODE = l_error_code;
```

Diagnostic `RAISE NOTICE` traces stay inline — they are debug breadcrumbs, not structured errors.

**Long parameter lists:** set each argument as a variable above the call, aligned, so the
block is copy-paste-friendly for repeated calls (logging, audit inserts):

```sql
l_log_run_id    := p_run_id;
l_log_component := l_component;
l_log_action    := 'load_start';
l_log_message   := format($$start for table '%1$s'$$, p_table_name);

CALL :schema_app_name.sp_ins_log_execution(l_log_run_id, l_log_component, l_log_action, l_log_message);
```

## Tabular alignment

Base indent is 3 spaces. Align names, types, `=` and `:=` on a common column (longest
identifier + gap).

- **Parameter signatures:** `(` and `)` each on their own line, leading comma, the mode
  keyword padded to a 6-char field.

```sql
CREATE OR REPLACE PROCEDURE :schema_app_name.sp_example
(
    IN    p_source_table_id            bigint
   ,IN    p_actor_email                varchar
   ,INOUT p_result                     text
)
```

- **Positional table aliases** `T01`, `T02`, ... for every aliased table reference,
  re-numbered from `T01` per statement. Why: aliases of different lengths (`pm`/`cp`/`sts`)
  break the column flush of the field names; equal-length `T0n` keep it.
- **JOINs spelled out** (`INNER JOIN`, not bare `JOIN`); `ON` on its own line; multiple
  conditions as an `AND`/`OR` river with the operator leading and the `=` aligned.

```sql
FROM
   :schema_app_name.project_member T01
   INNER JOIN :schema_app_name.account T02
   ON
     T02.id = T01.account_id
WHERE
       T01.project_id = l_project_id
   AND T02.status     = 'active'
```

## File numbering by table group

Each DDL file carries a 3-digit prefix that is a **table-group indicator**, not a global
sequence counter: `003.sp_ins_project.sql`.

- **One table = one number.** All objects of a table (table, policies, trigger function,
  trigger, procedures, seed) share the prefix. `003.sp_ins_project.sql` and
  `003.sp_upd_project.sql` deliberately share `003` — disambiguation is via the object
  name, not the number.
- **Numbers are never reassigned**, even when a table becomes obsolete.
- The number is only a filesystem sort helper. **Load order is decided by your deploy
  script**, not the number.

## Trigger functions: no DROP

Triggers depend on the function, so `DROP FUNCTION IF EXISTS` fails on re-run with
`cannot drop function ... because other objects depend on it (trigger ...)`. Use
`CREATE OR REPLACE FUNCTION` alone — it is trigger-safe as long as the signature stays
stable, which for `RETURNS TRIGGER` without parameters it does by definition. (Non-trigger
functions keep the `DROP FUNCTION IF EXISTS ... (signature);` + `CREATE OR REPLACE` pattern.)

```sql
CREATE OR REPLACE FUNCTION :schema_app_name.tf_table()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $triggerfunction$
BEGIN
   IF TG_OP = 'INSERT' THEN
      RETURN NEW;
   ELSIF TG_OP = 'UPDATE' THEN
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      RETURN OLD;
   ELSE
      RETURN NULL;
   END IF;
END;
$triggerfunction$;
```

In the trigger body: check `TG_OP` with `IF / ELSIF / ELSE`, cover all branches,
`ELSE → RETURN NULL` (no implicit fall-through). On INSERT pass `NEW.<column>` and
`RETURN NEW`; on DELETE pass `OLD.<column>` and `RETURN OLD`.

---

*Based on the article „SQL-Konventionen // PL/pgSQL-Prozeduren, die man in zwei Jahren
noch lesen kann" — sql.marcus-belz.de*