What a function page tells you

The symptom

A few invoices have the wrong total. The trigger invoice_lines_recalc on billing.invoice_lines calls billing.apply_discount, which someone who has left wrote in plpgsql. Before you call it by hand to see what it does, you want to know what it writes, and as whom.

What a function is

A function is code the server runs when a query calls it. apply_discount locks one invoice, checks that it is open, cuts total_cents by a percentage and returns the new total. The database decides who it runs as, what it can see, and how often the planner may call it, and the page is about those three things.

The header reads Function · plpgsql · owned by app · 2 overloads. The sections below are Pro. On Free the header keeps Function · plpgsql and the engine, without the owner or the overload count, and the page stops after Definition with the Pro sections named over a blurred silhouette (Object pages shows the gate). This function opens with two verdicts: Runs as its owner without a fixed search_path, in red, and Anyone who can connect can run it.

How to call it

The signature, one argument per line: invoice_id bigint, pct numeric = 10.0 marked optional, note text = 'a, b' marked optional, returning numeric. Beside it the summary in words: Takes invoice_id, pct and note (pct and note are optional) and returns a numeric. It changes data and runs with its owner's permissions (app).

Who can run it, and as whom

Four boxes in a row. Who can call it: PUBLIC, because new functions get that by default. Runs as: Owner (app), whoever calls it. search_path: Not pinned, in red. What it changes: Writes billing.invoices.

SECURITY DEFINER means the function runs as app, its owner, no matter who calls it. But search_path decides which round() an unqualified name means, and here it is whatever the caller set. A caller who puts a schema they own before pg_catalog can supply their own round(numeric), call apply_discount, and have their code run with app's permissions. Since PUBLIC can execute it, every role that can connect is such a caller.

The fix is two statements, and the page opens each in a console. Open the search_path fix in a console:

ALTER FUNCTION billing.apply_discount(invoice_id bigint, pct numeric, note text)
  SET search_path = billing, pg_temp;

pg_temp goes last so a temporary table can't shadow a real one. The PostgreSQL docs' stricter form, pg_catalog, pg_temp, works when the body qualifies every table, as here. Then Open REVOKE in a console:

REVOKE EXECUTE ON FUNCTION billing.apply_discount(invoice_id bigint, pct numeric, note text) FROM PUBLIC;

and grant EXECUTE to the one role that needs it.

Declared vs. body

What the function promises the planner, checked against what its code does. This one declares nothing, so the table folds to All defaults: VOLATILE · PARALLEL UNSAFE · CALLED ON NULL INPUT · NOT LEAKPROOF · COST 100.

Volatility is the one worth remembering. VOLATILE, the default, says the function can change data or give a different answer each time, so Postgres calls it again for every row. STABLE says the same arguments give the same answer within one statement, so the planner can call it once and use it in an index scan. IMMUTABLE says the same arguments give the same answer forever, so Postgres may precompute it and build an index on it. A function marked IMMUTABLE that reads a table keeps returning the old answer after the table changes, and the page says so in red.

How often it runs

Calls and time per call from pg_stat_user_functions. It needs track_functions set to pl or all. Here it is none, so the section says Postgres isn't counting function calls and shows the ALTER DATABASE … SET track_functions = 'pl' that turns it on.

Who uses it and what it touches

Used by on the left: the trigger invoice_lines_recalc, which runs it after insert or update for each row, and under Mentioned in code, billing.close_period(), drawn dashed because it was found by searching other functions' code, not recorded by Postgres. Touches on the right: billing.invoices, update, with a writes chip, and under Calls any function the body calls.

Try it, Body and the rest

Try it has one field per argument. Fill in an invoice id, leave pct empty and the default applies. The Call box shows the statement as it will run, wrapped in BEGIN and ROLLBACK because the body writes.

Body · 11 lines is the source, with a chip for each thing it does: update billing.invoices, reads billing.invoices, raises "invoice is not open". All properties and DDL fold out under it.

Caveats

• The body analysis is a reading of the text, so dynamic SQL and calls into other functions aren't followed.
• Mentioned in code searches other functions in this database. Application code is never searched, so an empty Used by doesn't mean unused.
• MySQL and MariaDB routines have a DEFINER and SQL SECURITY instead, and DETERMINISTIC there is advisory. SQLite has no functions page.