# ADR-008: PL/pgSQL RETURNS TABLE Functions Must Declare #variable_conflict use_column

## Status

Accepted, 2026-06-25.

## Status History

```yaml
status_history:
  - date: 2026-06-25
    status: Proposed
    changed_by: hkl
    reason: Formalising fix for recurring 42702 ambiguous column errors
    changed_via: adr-kit (360lm)
  - date: 2026-06-25
    status: Accepted
    changed_by: hkl
    reason: Applied to all affected functions; pattern prevents class of silent bugs
    changed_via: adr-kit (360lm)
```

## Context

In PostgreSQL PL/pgSQL, `RETURNS TABLE (col1 type, col2 type, ...)` implicitly creates OUT parameters with the same names as the declared columns. When the function body contains a `SELECT` or `FOR` loop that references a table with the same column names, PostgreSQL raises `42702: column reference "col_name" is ambiguous` because the column name matches both the OUT parameter and the table column. This error only appears at runtime (when the function is called), not at function creation time, making it easy to miss during development.

## Decision

Every PL/pgSQL function with `RETURNS TABLE` MUST include `#variable_conflict use_column` as the first line after `AS $$`. This directive tells PL/pgSQL to prefer the table column over the OUT parameter when a name is ambiguous.

```sql
CREATE OR REPLACE FUNCTION my_schema.my_func()
RETURNS TABLE (id int, name text)
LANGUAGE plpgsql
AS $$
#variable_conflict use_column
BEGIN
  RETURN QUERY SELECT t.id, t.name FROM my_table t;
END;
$$;
```

**Decision Maker:** hkl

## Alternatives Considered

- **Alias all table columns in SELECT (e.g., t.id instead of id).** Rejected: works but requires vigilance on every query inside the function; a single unqualified column reference re-introduces the bug. `#variable_conflict` is a one-line blanket fix.
- **Use RETURNS SETOF record with explicit column casting.** Rejected: loses type safety; callers must specify column names in AS clauses; more verbose.
- **Rename OUT parameters to avoid clashing with table columns.** Rejected: the OUT parameter names define the JSON keys returned to the API — renaming them changes the API contract.

## Consequences

**Positive:**
- Eliminates entire class of 42702 runtime errors in RETURNS TABLE functions.
- One-line fix, zero performance impact.
- Works with any combination of table column names.

**Negative / Trade-offs:**
- Slightly non-obvious for developers unfamiliar with PL/pgSQL variable conflict directives.
- The directive means table columns always win over OUT params in ambiguous references — requires care if you intentionally need to reference an OUT param by name inside the body (use explicit assignment instead).

**Risks and mitigations:**
- Forgetting the directive on a new function: 42702 error at runtime on first call. Mitigated: this ADR + code review. The error message is specific enough to diagnose quickly.

## Related Decisions

- ADR-007 (PG trigger SECURITY DEFINER) — related DB function pattern.

## References

- `memory/feedback_plpgsql_return_table_conflict.md` — original rule capture
- PostgreSQL docs: PL/pgSQL variable_conflict