Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Functions and Procedures in PostgreSQL: SQL vs PL/pgSQL, RETURNS TABLE, Volatility, SECURITY DEFINER and Transaction Control with CALL
In PostgreSQL a function returns rows or values, is called with SELECT and cannot end the transaction; a procedure is called with CALL and can COMMIT inside. An unmarked function counts as VOLATILE and blocks index use. Inlining of LANGUAGE sql functions, RETURNS TABLE, PARALLEL SAFE, an ISBN check in PL/pgSQL used in a CHECK constraint, stock reservation, error handling with SQLSTATE codes, SECURITY DEFINER and calling it all from Npgsql and Dapper.
PostgreSQL Triggers: BEFORE/AFTER, Row and Statement Level, Transition Tables, updated_at and Audit Triggers, Event Triggers
In PostgreSQL, changing the row itself belongs in a BEFORE ROW trigger through NEW, writing to other tables in an AFTER trigger; AFTER ROW triggers run once the statement has finished. updated_at with a WHEN condition, a jsonb audit trigger, the cost of row and transition-table statement triggers on a 200,000-row UPDATE, INSTEAD OF for a join view, an event trigger that records DDL, and reading trigger values back in EF Core with RETURNING.