SQL Conventions // PL/pgSQL Procedures You Can Still Read in Two Years

Published Jun 6, 2026
by

If you write a stored procedure, you write it for someone who doesn’t know it — usually for yourself, 18 months later, at 11 p.m., while an ETL run is stuck. Readability isn’t cosmetics, it’s debugging time. PostgreSQL forces almost nothing on you: names are free, indentation doesn’t matter, a RAISE EXCEPTION swallows any string you assemble inline. That’s exactly why procedure collections drift apart within a year without a convention — five developers, five styles, no shared search pattern.

This article distills a battle-tested set of PL/pgSQL conventions from a production ETL-generator project: a naming standard with prefixes, a collapsible block body structure, the principle “arguments as variables, not inline values”, and the tabular alignment that makes grep and code review efficient in the first place.

What you’ll take away:

  • a prefix system for procedures, functions, triggers and views (sp_, fn_, tf_, tr_, vw_) plus verb codes (ins/upd/del/dup/get);
  • the collapsible block body structure (Get name -> Initialize -> Check parameter -> Workload as standard blocks, extensible) — and why the input checks must come before the mutation;
  • the principle “arguments as variables, not inline values” — for error messages (format($$...$$)) as well as for long parameter lists of procedure calls;
  • tabular alignment for parameters, variables and JOINs with positional aliases (T01, T02);
  • a file numbering that maps table groups instead of a global sequence — and so avoids the renumbering tax;
  • why trigger functions get no DROP FUNCTION;
  • how this collection of conventions becomes a .claude/rules/ file that Claude Code auto-loads and enforces on every generated SQL line — the jump from a style-guide PDF to a machine-enforced rule.

Prerequisite: PostgreSQL 12+, basic PL/pgSQL knowledge (CREATE PROCEDURE/FUNCTION, DECLARE, EXCEPTION handlers).

Contents

Why PL/pgSQL conventions?

A single stored procedure is always readable — no matter what style it’s written in. The problem appears at scale: 50, 100, 200 objects across five developers. Without a shared vocabulary, nobody knows whether update_project is a procedure, a function, or a stray application-code name. Without tabular alignment, every code review first costs ten seconds of eye-sorting before the actual logic becomes visible. Without a file convention, grep doesn’t find what a procedure touches.

PL/pgSQL conventions are therefore not an end in themselves, but an investment in two recurring operations: searching (which object does what?) and reading-in-the-diff (what changed?). Both scale with the discipline of the writing. The rules below are distilled through that lens.

Naming standard

Every database object carries a type prefix. The prefix answers the question “what is this?” before you read the name:

Object typePrefixExample
Stored Proceduresp_sp_upd_table
Stored Functionfn_fn_is_null_or_empty
Trigger Functiontf_tf_table
Triggertr_tr_iud_table
Viewvw_vw_execution_duration

For procedures, the prefix is followed by a verb code, then the entity:

  • ins = insert, upd = update, del = delete
  • get = select, exe = execute
  • dup = duplicate (copy a row to a new row, with a new surrogate key — for “save as” / duplicate flows, e.g. sp_dup_project)

For triggers, the <type> part encodes the covered events: i (insert), u (update), d (delete) or the combination iud. So a tr_iud_table fires on all three.

Cross-cutting rules that keep the whole thing consistent:

  • Always snake_case, always singular table names (user, project, task — never users). FK columns follow naturally: user_id, not users_id.
  • Timestamp columns end in _on, never _at: created_on, modified_on, last_login_on. The TypeScript mapping keeps the same suffix (createdOn).
  • Every table gets a surrogate PK id bigserial NOT NULL with CONSTRAINT pk_<table> PRIMARY KEY (id). Natural keys become UNIQUE constraints, not the PK.
  • Prefix parameters with p_ (p_project_id, p_actor_email), local variables with l_. The mode (IN/OUT/INOUT) carries the direction — not the name (p_result, not p_out_result).
  • Never hard-code schema names — always via a parameter or a psql variable (:schema_app_name).

One special case deserves mention: the table is named account, not user. PostgreSQL treats USER as a reserved keyword (a synonym for CURRENT_USER); a bare FROM user parses as a function call. Instead of relying on schema-qualification everywhere, the table name steps aside — the domain language stays “User”, account is a DB-side workaround.

A word on perspective: not every one of these rules is a law of nature. snake_case, the p_/l_ prefixes and spelled-out schema variables are solid readability and portability arguments. The _on suffix, the always-surrogate PK or the account naming, by contrast, are house style — defensible decisions another team could decide differently. But that’s exactly the point: which form is chosen is secondary — that it’s chosen the same everywhere is decisive. Only consistency turns _on, p_ or T0n into a reliable search pattern. A mediocre convention, applied consistently, beats the best one applied half the time.

The block body structure

Every procedure/function body is split into clearly delimited sections, each introduced by an 80-dash banner and — except the first — wrapped in its own BEGIN ... END; sub-block. The sub-block has only one purpose: it makes the section collapsible in the editor, so you can focus on one part. The four standard blocks:

  1: BEGIN
  2:    -- --------------------------------------------------------------------------------
  3:    -- Get name of function/procedure
  4:    -- --------------------------------------------------------------------------------
  5:    SET LOCAL lc_messages TO 'C';   -- force English server messages
  6:    GET DIAGNOSTICS l_context = PG_CONTEXT;
  7:    l_component := substring(l_context from 'function (.*?)\(');
  8: 
  9:    RAISE NOTICE '### procedure : %', l_component;
 10: 
 11:    -- --------------------------------------------------------------------------------
 12:    -- Initialize variables
 13:    -- --------------------------------------------------------------------------------
 14:    BEGIN
 15:       l_status_new := 'active';        -- hard-coded initialization
 16: 
 17:       SELECT T01.default_role_code     -- or read from a table
 18:         INTO l_default_role_code
 19:         FROM app.app_config T01
 20:        WHERE T01.config_key = 'default_role';
 21:    END;
 22: 
 23:    -- --------------------------------------------------------------------------------
 24:    -- Check parameter
 25:    -- --------------------------------------------------------------------------------
 26:    BEGIN
 27:       -- all input checks: parameter validation,
 28:       -- actor context, permission preconditions
 29:       NULL;
 30:    END;
 31: 
 32:    -- --------------------------------------------------------------------------------
 33:    -- Workload
 34:    -- --------------------------------------------------------------------------------
 35:    BEGIN
 36:       -- the actual work: lookups, mutations, RETURN
 37:       NULL;
 38:    END;
 39: END;

  1. Get name — derives the function’s own name via GET DIAGNOSTICS ... PG_CONTEXT for meaningful error prefixes and an entry RAISE NOTICE trace. Sits directly in the outer BEGIN (no sub-block). The PG_CONTEXT trick is convenience, not a must — drop the block if you don’t need it, or set the component name as a plain constant.
  2. Initialize — sub-block that fills the local variables the rest of the procedure needs. Two sources: hard-coded start values (l_status_new := 'active') or read from a table via SELECT ... INTO (e.g. a default from a configuration table).
  3. Check parameter — sub-block with all input and precondition checks up front: parameter validation, actor context, permission checks.
  4. Workload — sub-block with the actual work (lookups, mutations, RETURN).

Four standard blocks, but no fixed count. The actual principle isn’t “exactly these four”, but: put related statements into one BEGIN ... END;-wrapped, collapsible block, introduced by a banner comment. A procedure may have as many blocks as its task requires — for instance one block per logging step (see Arguments as variables). Small procedures get by with fewer; the Get name block always stays directly in the outer BEGIN, all others are sub-blocks that fold individually.

The most important ordering rule: checks come before the mutation. The Check parameter block always comes before the Workload block. When reworking existing procedures, the order of security-relevant checks — permission checks above all — must never slip behind the mutation. Otherwise the procedure writes first and only then checks whether it was allowed to.

And a practical pitfall: a BEGIN END; without a statement is a syntax error in PL/pgSQL. Empty grouping blocks need at least a NULL;.

Arguments as variables, not inline values

A recurring principle: values aren’t written directly into a call, but assigned to a variable first — the call receives only variables. This separates the what (which value) from the how (the call itself) and keeps the call line readable. Two places where this pays off most: error messages and long parameter lists.

Error messages via format(). Hard-coded error texts right at the RAISE mix the message construction with the act of throwing. The convention puts message and error code into variables first and only then passes them on:

  1: DECLARE
  2:    l_component       text;
  3:    l_error_message   text;
  4:    l_error_code      text;
  5: BEGIN
  6:    -- ...
  7:    IF NOT l_can_edit THEN
  8:       l_error_message := format($$%1$s: actor='%2$s' is neither owner nor editor of project id=%3$s$$, l_component, p_actor_email, l_project_id);
  9:       l_error_code    := 'insufficient_privilege';
 10: 
 11:       RAISE EXCEPTION USING MESSAGE = l_error_message, ERRCODE = l_error_code;
 12:    END IF;

The rules behind it:

  • Dollar-quoting as the template delimiter (format($$...$$, ...)) — this keeps the quotes around text values single ('%2$s' instead of doubled). Safe inside the procedure body because it is quoted with $procedure$...$procedure$.
  • Indexed placeholders only %1$s, %2$s, %3$s — never the bare %. This is indispensable as soon as an argument appears more than once in the message: %n$s references the same argument in several places without passing it again.
  • Text values in single quotes ('%2$s'), so strings are visually set off. Numeric values without quotes. The component prefix (l_component) stays unquoted.
  • Keep the ERRCODE, never invent one. If the original had no ERRCODE, it stays without.

The code then reads top to bottom: first what is thrown, then that it’s thrown. One exception remains the diagnostic RAISE NOTICE traces (the ### procedure entry trace and the SQLERRM trace in the exception handler) — they are debug breadcrumbs, not structured errors, and stay plain inline RAISE NOTICE.

Long parameter lists in procedure/function calls. The same principle applies as soon as a call has more than two or three arguments. Inline values right in the CALL quickly become a comma-separated wall in which nobody can tell which value belongs to which parameter. Instead, set one variable per argument before the call — the assignments stand aligned underneath one another, and the call receives only the variables:

  1: -- log record: run start
  2: l_log_run_id    := p_run_id;
  3: l_log_component := l_component;
  4: l_log_action    := 'load_start';
  5: l_log_table     := p_table_name;
  6: l_log_message   := format($$start for table '%1$s'$$, p_table_name);
  7: 
  8: CALL app.sp_ins_log_execution(l_log_run_id, l_log_component, l_log_action, l_log_table, l_log_message);

The cost is real — every value costs a declaration and an assignment. The gain outweighs it: the call becomes the same line every time, and the block above it is copy-paste-friendly. One logging step looks like the next — insert a record, update it later — so you copy the block and only adjust the assignments instead of re-sorting a long argument list. For repeated calls (logging, audit inserts, recurring status updates), that’s the difference between readable repetition and a comma-separated wall.

Tabular alignment

This is where the biggest reading gain per unit of discipline sits. The base indentation is three spaces app-wide. The sub-columns (name, type) align on a shared column — the length of the longest identifier plus a gap.

Anyone who has ever built a report in Excel already knows the principle: text is left-aligned, numbers right-aligned, amounts aligned on the decimal point, some things centered. Nobody would leave a column of numbers left-aligned — the eye needs the aligned edge to compare values row by row instead of reading each cell individually. In code it’s the same mechanics: aligned columns turn a list of declarations, assignments or JOIN conditions into a table you scan at a glance — which is exactly why it’s called tabular alignment.

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

  1: CREATE OR REPLACE PROCEDURE :schema_app_name.sp_example
  2: (
  3:     IN    p_source_table_id            bigint
  4:    ,IN    p_actor_email                varchar
  5:    ,INOUT p_result                     text
  6: )

Variable declarations: data type aligned on a shared column:

  1: DECLARE
  2:    l_context                 varchar;
  3:    l_session_actor           varchar;
  4:    l_error_message           text;

Variable initialization: several assignments as one coherent block — the := line up, even with differently long variable names:

  1: l_run_id        := p_run_id;
  2: l_component     := substring(l_context from 'function (.*?)\(');
  3: l_table_name    := p_table_name;
  4: l_status_new    := 'active';

The same column flush as in the declarations: the aligned := show at a glance that this is one coherent initialization block, and make copy-paste adjustment easier (see Arguments as variables).

JOINs deserve special mention, because this is where most styles diverge:

  1: FROM
  2:    app.project_member T01
  3:    INNER JOIN app.account T02
  4:    ON
  5:      T02.id = T01.account_id
  6: WHERE
  7:        T01.project_id = l_project_id
  8:    AND T02.status     = 'active'

  • Spell JOINs out — INNER JOIN instead of a bare JOIN.
  • Positional aliases T01, T02, T03 for every aliased table reference, renumbered from T01 per statement. This seems counter-intuitive at first — wouldn’t meaningful aliases be more readable? In practice, no: differently long aliases (pm/cp/sts) break the tabular alignment of the field names, and with many JOINs meaningful names lose their expressiveness anyway. Equal-length T0n keep the column flush.
  • ON on its own line, aligned under INNER. A single condition indented two spaces; multiple conditions as an AND/OR river with a leading operator and the = aligned underneath.

The complete rulebook for the structure of SQL statements — indentation of field lists, JOIN notation, the WHERE river and the tabular layout — is a topic of its own, derived in detail in Formatting SQL Statements (Part 2) — Statement Structure: SELECT, WHERE, FROM, JOIN. Here it’s only about alignment inside procedure code.

File numbering by table group

Every DDL file carries a 3-digit number prefix: 003.sp_ins_project.sql. The trick: the prefix is not a global sequence counter, but a table-group indicator.

  • 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 the same number — disambiguation is via the object name, not the number.
  • Numbers are never reassigned. When a table becomes obsolete, its number stays burned.

Why not a global 001., 002., 003. counter? Three concrete advantages:

  1. Co-location: ls 007.* shows all objects of a table at a glance.
  2. No renumbering tax: a new procedure for project_member? It’s called 004.sp_new_procedure.sql, done — no 20 other files need renaming.
  3. Dependencies are resolved by the deploy script: tables -> policies -> functions -> procedures -> triggers are loaded in separate blocks. The number is only a sort helper for the filesystem listing, not a semantic load order.

For cross-table objects (a procedure touches several tables) a heuristic applies: trigger function -> prefix of the table whose trigger it hangs on. Procedure with a clear write target -> prefix of that table (reads from other tables don’t count). The file’s --comment: header names every cross-table relationship explicitly, so that grep finds what only the prefix filter would otherwise show.

Trigger functions: no DROP

One last point, often learned the painful way. Non-trigger functions get the DROP FUNCTION IF EXISTS ... (signature); + CREATE OR REPLACE pattern. Trigger functions don’t:

  1: \echo "## CREATE FUNCTION :schema_app_name.tf_table()"
  2: 
  3: CREATE OR REPLACE FUNCTION :schema_app_name.tf_table()
  4: RETURNS TRIGGER
  5: LANGUAGE plpgsql
  6: AS $triggerfunction$
  7: BEGIN
  8:    -- Logic
  9: END;
 10: $triggerfunction$;

The reason: triggers depend on the function. A DROP FUNCTION IF EXISTS aborts on re-run with cannot drop function ... because other objects depend on it (trigger ...). CREATE OR REPLACE FUNCTION alone is trigger-safe, as long as the signature stays stable — and for RETURNS TRIGGER without parameters it is, by definition.

In the trigger body itself: check TG_OP with IF / ELSIF / ELSE, always cover all three branches, ELSE -> RETURN NULL (no implicit fall-through). On INSERT pass NEW.<column> and RETURN NEW, on DELETE OLD.<column> and RETURN OLD. TG_OP always goes as the first argument into called procedures.

Formatting is understanding, even in the age of Copilot and Claude

There are tools that produce layout automatically: pgFormatter for Postgres, sqlfluff as a linter and formatter across several dialects. They indent, wrap lines, normalize case — in seconds, without discipline. So why format by hand at all?

Because formatting is more than layout. The act of manually indenting, aligning and setting blocks forces you to read the statement in full and to build the relationships between the tables mentally. Whoever splits a procedure into Get name / Check parameter / Workload has to decide what is a check and what is the actual work — and notices in the process when a permission check sits in the wrong place (see the before example below). Auto-formatters produce the layout, but not this understanding.

In the age of Copilot, Cursor and Claude Code this is doubly relevant. Generated code arrives fully formatted — and that’s exactly the trap: technically correct SQL that still doesn’t answer the business question. Conventions help on two levels: they make generated code readable enough to review — and, as the next section shows, they can be handed to the AI as a rule, so it generates in the house style from the start. The understanding, though, stays with the human.

Conventions as a rules file: enforced, not documented

Up to here it was all a classic style guide — good, but with the usual problem: a style guide nobody reads is ineffective. Conventions rot the moment they sit in a wiki and depend on discipline. This is where the decisive step comes in: this whole collection doesn’t live as a PDF, but as a file .claude/rules/sql.md in the repository — and is loaded by Claude Code (Anthropic’s AI coding agent) automatically as a project instruction in every session.

The difference is fundamental. A human developer can forget the style guide. The agent gets it served on every request — and writes sp_upd_project instead of update_project, indents JOINs with T01/T02 and puts error messages into l_error_message variables, because the rule says so. “Please stick to it” becomes “this is how it’s generated”.

What a rules file does differently for an agent than for a human

A convention for humans may be terse — the reader fills gaps with experience. A convention for an agent must do three additional things, otherwise generation drifts:

  1. Explicit examples instead of prose. Instead of “align JOINs in tabular form”, the file holds the complete, correctly indented code block — the agent imitates the pattern it sees. Every rule is anchored with a do-example.
  2. Don'ts as their own category. The file lists not only what to do, but explicitly the anti-patterns: “DROP FUNCTION for trigger functions”, “global sequence counter instead of table-group number”. Negative examples catch exactly the plausible missteps a model otherwise tends toward.
  3. Single source of truth references. Where a rule applies in several places, the file points to the one authoritative source (e.g. “load order is determined solely by deploy.sql, the file number is only a sort helper”). This prevents the agent from mixing two contradictory interpretations.

The structure that carries the enforcement

This turns the file itself into a small specification format. Every rule follows the same triple: Rule -> Reason -> Example (Do/Don’t). The reason is no decoration — it tells the agent why, so it can transfer the rule to new cases the file doesn’t spell out. An example from the sql.md:

Rule: Positional aliases T01, T02 instead of meaningful names. Reason: differently long aliases (pm/cp/sts) break the tabular alignment of the field names; equal-length T0n keep the column flush. Do: the aligned JOIN block. Don’t: the bare JOIN with pm/cp.

When the agent later hits a JOIN with three tables that the file doesn’t show literally, it derives the correct T03 continuation from the reason. The why line is the generalization bridge.

What that means in everyday ETL work

In production this means: new stored procedures come out convention-compliant on the first try. No code review that flags formatting before the logic gets a look. No five styles across five developers — the agent is the sixth, always-disciplined “developer” that never forgets the file. And when the convention changes (say, a new verb code mrg for merge operations), one file is edited — and from the next session the agent generates by the new rule.

That’s the real lever: a convention is only worth as much as its enforcement. As a .claude/rules/ file, enforcement moves from “human discipline in review” to “default at generation time” — and right there, at the point where the code is born, it’s cheapest.

This single rules file is just one of three levers. How rules, skills and agents together carry AI-assisted SQL development is laid out in the overview article AI-Assisted SQL Development with Claude Code — this post is its case study.

Take it with you: the conventions distilled in this article are available as a ready-made starter rules file to download — drop it into your repo as .claude/rules/sql.md, and Claude Code picks it up as a project instruction from the next session on.

The complete starter rules file is available as a download — drop it in as .claude/rules/sql.md.

Before and after: one procedure, twice

The individual rules only take effect together. Here is a procedure that changes a project member’s role — once the way it’s typically dashed off, once following this article’s conventions.

Before — it runs, but it’s hard to read and has a dangerous bug:

  1: create or replace procedure update_member_role(pid bigint, aid bigint, newrole varchar, actor varchar)
  2: language plpgsql as $$
  3: declare canedit boolean;
  4: begin
  5:   select exists(select 1 from project_member pm join account a on a.id=pm.account_id
  6:     where pm.project_id=pid and a.email=actor and pm.role_code in ('owner','editor')) into canedit;
  7:   update project_member set role_code=newrole, modified_on=now(), modified_by=actor
  8:     where project_id=pid and account_id=aid;
  9:   if not canedit then raise exception 'user % not allowed', actor; end if;
 10: end; $$;

Three problems: (a) the name update_member_role doesn’t reveal that it’s a procedure, and the parameters (pid, aid, newrole) are cryptic. (b) No block, no alignment — you have to decipher every line individually. (c) The serious one: the UPDATE (line 7) runs before the permission check (line 9). The procedure writes first and only then checks whether the caller was even allowed to.

After — the same logic, convention-compliant:

  1: CREATE OR REPLACE PROCEDURE :schema_app_name.sp_upd_project_member_role
  2: (
  3:     IN p_project_id   bigint
  4:    ,IN p_account_id   bigint
  5:    ,IN p_role_code    varchar
  6:    ,IN p_actor_email  varchar
  7: )
  8: LANGUAGE plpgsql
  9: AS $procedure$
 10: DECLARE
 11:    l_context         text;
 12:    l_component       text;
 13:    l_can_edit        boolean;
 14:    l_error_message   text;
 15:    l_error_code      text;
 16: BEGIN
 17:    -- --------------------------------------------------------------------------------
 18:    -- Get name of procedure
 19:    -- --------------------------------------------------------------------------------
 20:    GET DIAGNOSTICS l_context = PG_CONTEXT;
 21:    l_component := substring(l_context from 'function (.*?)\(');
 22: 
 23:    -- --------------------------------------------------------------------------------
 24:    -- Check parameter
 25:    -- --------------------------------------------------------------------------------
 26:    BEGIN
 27:       SELECT EXISTS (
 28:          SELECT 1
 29:          FROM
 30:             app.project_member T01
 31:             INNER JOIN app.account T02
 32:             ON
 33:               T02.id = T01.account_id
 34:          WHERE
 35:                T01.project_id = p_project_id
 36:             AND T02.email      = p_actor_email
 37:             AND T01.role_code IN ('owner', 'editor')
 38:       )
 39:       INTO l_can_edit;
 40: 
 41:       IF NOT l_can_edit THEN
 42:          l_error_message := format($$%1$s: actor='%2$s' may not edit project id=%3$s$$, l_component, p_actor_email, p_project_id);
 43:          l_error_code    := 'insufficient_privilege';
 44: 
 45:          RAISE EXCEPTION USING MESSAGE = l_error_message, ERRCODE = l_error_code;
 46:       END IF;
 47:    END;
 48: 
 49:    -- --------------------------------------------------------------------------------
 50:    -- Workload
 51:    -- --------------------------------------------------------------------------------
 52:    BEGIN
 53:       UPDATE app.project_member T01
 54:       SET
 55:           role_code   = p_role_code
 56:          ,modified_on = now()
 57:          ,modified_by = p_actor_email
 58:       WHERE
 59:             T01.project_id = p_project_id
 60:         AND T01.account_id = p_account_id;
 61:    END;
 62: END;
 63: $procedure$;

What changed, point by point: sp_upd_project_member_role carries a prefix + verb code and thereby says what it is; the p_ parameters speak for themselves; the BEGIN ... END; blocks separate check from work and fold in the editor; the permission check now sits before the mutation (Check parameter before Workload); the error message lives in a variable instead of inline at the RAISE; JOIN and WHERE are tabularly aligned with T01/T02. An Initialize block is deliberately missing here — there’s nothing to preset, and the block structure simply isn’t fixed to a particular count.

Both procedures do exactly the same thing. The second one you can read in 18 months at 11 p.m. — and immediately see that it checks before it writes.

Summary

PL/pgSQL gives you almost no structure — which is freedom as long as one human writes a procedure, and a problem the moment five humans maintain 200 objects. The set of PL/pgSQL conventions shown here addresses exactly the two operations that scale with the codebase: searching (prefixes, table-group numbering, cross-table headers) and reading-in-the-diff (block body structure, tabular alignment, arguments-as-variables).

Take-away:

  • Prefix + verb code (sp_upd_project) answers “what is this?” before the first reading of the name. snake_case, singular, _on suffix, p_/l_ variables are the cross-cutting discipline underneath.
  • The collapsible block body structure (Get name -> Initialize -> Check parameter -> Workload as standard blocks, extensible) makes procedures collapsible and enforces: checks before the mutation, permission checks never behind the write.
  • Arguments as variables instead of inline values — for error messages (format($$...$$) with indexed placeholders %1$s) as well as for long parameter lists — separates the what from the how and makes uniform calls copy-paste-friendly.
  • Tabular alignment with positional aliases (T01) keeps the column flush that meaningful aliases would break.
  • File numbers map table groups, not a global sequence — that saves the renumbering tax on every new procedure.
  • Trigger functions get no DROP — CREATE OR REPLACE alone is trigger-safe.
  • As .claude/rules/sql.md, the whole collection is auto-loaded by Claude Code and enforced at generation time — the key to that is the triple Rule -> Reason -> Do/Don’t example, because the why line lets the agent generalize to new cases.

FAQ

Why positional aliases T01/T02 instead of meaningful names like pm/cp?

Because differently long aliases break the tabular alignment of the field names — and that alignment is precisely the reading gain. Equal-length T0n keep the columns lined up. With many JOINs, meaningful aliases lose their expressiveness anyway (“was cp connection_profile or column_property?”). The convention trades supposed self-documentation for reliable visual structure. The same alignment logic underlies statement formatting — derived in detail in Formatting SQL Statements (Part 2).

Isn’t the block structure overhead for small procedures?

The number of blocks follows the procedure, not the other way around: a small procedure just has a single Workload block, and a pure validator function without an error RAISE (e.g. fn_validate_* that only returns marker strings) skips Get name and initialization entirely. But as soon as a mutation is in play, the separation pays off immediately: the Check parameter block is the guaranteed place every reviewer looks first for the permission checks. That predictability is worth more than the few saved banner lines — and the collapsed BEGIN ... END; blocks make even a long procedure fit on a screen.

Why format by hand at all — pgFormatter and sqlfluff do it automatically, don’t they?

Auto-formatters (pgFormatter, sqlfluff) produce layout, but no understanding. Structuring by hand forces you to read the statement in full and build the table relationships mentally — that’s exactly when you notice a permission check in the wrong place or a JOIN that returns too many rows. A formatter would have indented the code nicely and formatted the bug right along with it. Use the tools in addition for mechanical consistency by all means — but they replace neither the reading nor the convention decisions (prefixes, block structure, T0n aliases) that no formatter makes for you. More on this under Formatting is understanding.

What if an input check and a lookup are intertwined — say a permission check that first needs a previously loaded project_id?

Then the boundary lies after the last pure input check; any check that builds on a lookup stays in the Workload block. The sub-blocks wrap existing statements in place — nothing is re-sorted. Better a smaller Check parameter block than a risky reordering that accidentally pushes a permission check behind a mutation.

Why format($$...$$) and not simply RAISE EXCEPTION 'text %', var?

Three reasons. First, the variable separates the what from the how — the code reads top to bottom. Second, dollar-quoting allows single instead of doubled quotes around text values. Third — and that’s the hard advantage — indexed placeholders (%2$s) reference the same argument in several places without passing it again. As soon as a message names a value twice, the bare % is no longer maintainable.

Does this apply to SQL Server / T-SQL too?

The principles (prefixes, check-before-mutation, arguments-as-variables, alignment) are engine-agnostic. The syntax isn’t: T-SQL has no format() equivalent (you build strings with FORMATMESSAGE or CONCAT), no dollar quotes, and a different trigger model (AFTER/INSTEAD OF instead of BEFORE/AFTER with TG_OP). The convention idea transfers, the concrete code snippets don’t, 1:1.

How does a rules file for an AI agent differ from a normal style guide?

In three ways. First, examples instead of prose — the agent imitates visible code, so each rule carries a full do-block. Second, explicit Don'ts — negative examples catch the plausible missteps a model otherwise tends toward (such as DROP FUNCTION on trigger functions). Third, one reason line per rule — the why is the bridge over which the agent transfers a rule to cases the file doesn’t show literally. A human reader fills those gaps with experience; the agent needs them spelled out. In practice the file lands under .claude/rules/ in the repo and is loaded as a project instruction every session — “please comply” thereby becomes “this is how it’s generated”.

AI-assisted development:

SQL structure & formatting:

ETL context:

Data quality & type conversion: