Using AI to Write PostgreSQL Stored Procedures

Good at the Shape, Careless at the Edges

Language models write competent PL/pgSQL. They get the structure right — the function/trigger split, DECLARE blocks, exception handling, the dollar-quoted body — because that structure is well represented in training data.

Where they get careless is precisely where PostgreSQL differs from the databases they saw more of. Knowing that list turns review from a hunt into a checklist.

The Review Checklist

• Volatility. Generated functions are almost always unmarked, defaulting to VOLATILE. A genuinely IMMUTABLE or STABLE function marked volatile blocks index usage and re-evaluates per row.
• Trigger return values. A BEFORE row trigger must RETURN NEW or the row silently vanishes. Models sometimes emit RETURN NULL from a template.
• Dynamic SQL quoting. String concatenation instead of format() with %I and %L. This is an injection bug, not a style preference.
• search_path. SECURITY DEFINER without a pinned search_path is a privilege-escalation vector. Always set it explicitly.
• Exception blocks that swallow. A bare EXCEPTION WHEN OTHERS THEN NULL turns a failure into silent data loss. It also opens a subtransaction per call, which is a real cost inside a loop.
• Row-at-a-time loops. Models reproduce the cursor-loop idiom faithfully. Most of the time a single set-based statement replaces it and runs orders of magnitude faster.

What Careless Looks Like

-- generated: three problems
CREATE FUNCTION apply_raise(p_dept text) RETURNS void AS $$
BEGIN
  EXECUTE 'UPDATE emp SET salary = salary*1.1 WHERE dept = ''' || p_dept || '''';
EXCEPTION WHEN OTHERS THEN NULL;
END; $$ LANGUAGE plpgsql;

-- fixed: parameterised, no swallowing, explicit volatility
CREATE FUNCTION apply_raise(p_dept text) RETURNS void AS $$
BEGIN
  EXECUTE format('UPDATE emp SET salary = salary*1.1 WHERE dept = %L', p_dept);
END; $$ LANGUAGE plpgsql VOLATILE SECURITY INVOKER SET search_path = public;

In a Migration, Prefer Rules

Converting existing PL/SQL is a different task from writing new procedures, and it should be handled differently. The mapping from NVL to COALESCE, :NEW to NEW, EXECUTE IMMEDIATE to EXECUTE is finite and known — encode it once and it is right every time.

Reserve the model for the parts that genuinely need interpretation: explaining what an undocumented package was for, or proposing a redesign for a construct with no equivalent. Determinism where the answer is known, judgement where it isn’t.