Stored procedure
A named routine that runs inside the database, useful when one controlled call must perform several data operations.
See it
What it is
A stored procedure is a named routine saved in the database and run with parameters. It can read and change data close to where that data lives, which is useful when several statements must follow one carefully controlled path. PostgreSQL procedures are created with CREATE PROCEDURE and invoked with CALL. Plenty of 1990s enterprise systems put most of their business rules here, and a lot of that code is still in production.
Reach for one when a database operation needs a stable entry point, tight permissions, or many data-heavy steps without repeated network round trips. Batch maintenance and privileged administrative actions are common fits. Keep ordinary product behavior in the application when it changes often or needs services outside the database.
Gotcha: procedure syntax, transaction rules, and error behavior vary sharply between PostgreSQL, MySQL, SQL Server, and Oracle. The code is also easy to miss in an application review. Version it in migrations, test it against the real database engine, and be especially cautious with SECURITY DEFINER because a careless search_path can turn it into a privilege escalation.
Ask AI for it
Implement this operation as a PostgreSQL 16 PL/pgSQL stored procedure created with CREATE PROCEDURE and called with CALL. Give every input an explicit type, validate it before writing, keep the multi-table change atomic, and raise errors with useful SQLSTATE codes. If SECURITY DEFINER is required, set a safe fixed search_path and revoke PUBLIC execution. Return the migration and rollback SQL plus pgTAP tests for success, invalid input, permission denial, and rollback on failure.