Functions in SQL and PL/pgSQL

Reusable logic inside the database: SQL functions, PL/pgSQL with variables and control flow, and set-returning functions.

Functions keep logic next to the data: calculations every application must agree on, validation, and operations that would otherwise need many round trips. PostgreSQL supports functions in SQL, PL/pgSQL (its procedural language), and with extensions in Python, Perl and others.

Three kinds of function

annual_cost is a SQL function marked IMMUTABLE (same input, same output), so the planner can inline and optimize it.

project_health uses PL/pgSQL: DECLARE variables, SELECT ... INTO, IF / ELSIF / ELSE, RAISE NOTICE for messages.

The RISC-V project has 1480 hours across 3 people (493 per person), so it is reported as overloaded.

team_of RETURNS TABLE, so it can be used in FROM like a table.

Functions vs procedures

EXECUTE 'SELECT * FROM ' || p_table || ' WHERE name = ''' || p_name || '''';

EXECUTE format('SELECT * FROM %I WHERE name = $1', p_table) USING p_name;

Building dynamic SQL by string concatenation allows SQL injection. format() with %I quotes identifiers, and USING passes values as parameters.

What is the difference between a function and a procedure in PostgreSQL?

Functions return a value and run within the calling statement's transaction. Procedures are invoked with CALL, return no value (OUT parameters aside), and can commit or roll back transactions inside their body.

Why must a function used in an index expression be IMMUTABLE?

The index stores precomputed results; if the function could return something different later for the same input, the index would become wrong.