SQL is declarative, but Postgres provides an alternative language for database-side programming called PgSQL[1]. PgSQL adds some imperative features, like FOR l

SQL is declarative, but Postgres provides an alternative language for database-side programming called PgSQL[1]. PgSQL adds some imperative features, like FOR loops and variables, as a superset of SQL's features. So functions in the database can be written in this imperative language (or in SQL), and integrated into features like database triggers or called be clients (or, commonly, used to write pg_cron jobs). And your understanding of the distinction between them is correct. Declarative describes what you want to do but not how you want to do it, and Postgres' query planner (or that any fully featured SQL database) will uses statistics it has recorded about your data and indexes you've created to optimize the execution of your query (decide how to do it). PgSQL can tell Postgres what to do and how, except in the places where you're writing declarative SQL statements (since it's a superset). Breaking things up into smaller units like that limits the scope the planner can optimize over, which may defeat or limit certain optimizations and you may end up with something less performant. For some straightforward examples: if you write a min or max function with a FOR loop, you have written an O(n) implementation for something the query planner can sometimes do in O(1) using an index. If you write a sum function, you've written a sequential implementation of something the query planner may be able to parallelize. [1] https://www.postgresql.org/docs/current/plpgsql.html https://www.postgresql.org/docs/current/plpgsql.html