skip to content

In PostgreSQL's PL/pgSQL, how is a block structured, and what do %TYPE and %ROWTYPE give your variable declarations?

level: juniorimportance: should knowfreq 52%

answer

  1. label, DECLARE, BEGIN, END
  2. BEGIN here is not a transaction
  3. undefaulted variables start as NULL
  4. borrow a column's type
  5. a whole table row in one variable

basics

~20 s

A PL/pgSQL block is an optional label and DECLARE section, then BEGIN statements, an optional EXCEPTION section and END; BEGIN/END only group code. %TYPE copies a column's or variable's type, %ROWTYPE a table's whole row type.

solid answer

~40 s

Every PL/pgSQL body is one block: `[<<label>>] [DECLARE ...] BEGIN ... [EXCEPTION ...] END`, and blocks nest, each with its own variable scope. `BEGIN`/`END` only group statements; they do not start or commit a transaction, which is the commonest beginner confusion. Variables are declared in `DECLARE`, start as NULL unless given a default, and that default is re-evaluated every time the block is entered. `%TYPE` declares a variable with the type of a column, such as `loyalty_member.points_balance%TYPE`, or of another variable, so the code follows the schema. `%ROWTYPE` declares a variable that holds a whole table row, with fields read as `v_member.status`. A `DO` block runs the same language once, with no parameters and no result, which suits one-off scripts.

code

sql · 26 lines
sql
CREATE FUNCTION member_points(p_member_id bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $$
<<outer_blk>>
DECLARE
  v_member     loyalty_member%ROWTYPE;
  v_checked_at CONSTANT timestamptz := now();
BEGIN
  SELECT * INTO v_member
    FROM loyalty_member
   WHERE member_id = p_member_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'member % does not exist (checked at %)',
      p_member_id, v_checked_at;
  END IF;
  RETURN v_member.points_balance;
END outer_blk;
$$;

DO $$
BEGIN
  UPDATE loyalty_member SET tier = 'silver' WHERE tier IS NULL;
  RAISE NOTICE 'back-filled tiers';
END;
$$;

go deeper

for a junior

Recall the block skeleton, that BEGIN and END only group statements, and what %TYPE and %ROWTYPE declare. Being able to write a small function with a row variable is the bar.

for a middle

Explain variable scope across nested blocks, label qualification, defaults evaluated on each block entry, and why a record variable adapts while a %ROWTYPE variable must match its table.

for a senior

Show you know an EXCEPTION section turns a block into a costly subtransaction, and when DO with COMMIT is allowed. Tie %TYPE to keeping functions stable through schema changes.

for a principal

Frame block design as maintainability: conventions for naming, typing variables from the schema, and keeping one-off DO scripts reviewed and repeatable rather than ad hoc.

## The block is the unit of PL/pgSQL PL/pgSQL is PostgreSQL's built-in procedural language, installed by default in every database. Its code is organised in **blocks**, and the whole body of a function, a procedure or a `DO` command must be exactly one block: ```sql [ <<label>> ] [ DECLARE declarations ] BEGIN statements [ EXCEPTION WHEN condition THEN handler_statements ] END [ label ]; ``` Blocks nest: a statement position can hold another `BEGIN ... END;` block with its own `DECLARE` section and its own variable scope. An inner variable with the same name as an outer one **shadows** it; the outer one stays reachable by qualifying it with the outer block's label, as in `outer_blk.v_total`. Labels also let `EXIT` leave a specific loop or block. Each declaration and statement ends with a semicolon, a nested block's `END` needs one too, and only the final `END` of a function body may omit it. ## What goes in DECLARE A declaration reads `name [CONSTANT] type [NOT NULL] [{DEFAULT | := | =} expression];`. The rules interviewers like to probe: - A variable with no default starts as the SQL **null value**, so adding to an uninitialised counter yields NULL, not a number. - The default expression is **evaluated each time the block is entered**, not once per function call; a nested block inside a loop re-initialises its variables on every iteration. - `CONSTANT` forbids assignment after initialisation for the duration of the block. - `NOT NULL` turns assigning NULL into a run-time error, and such a variable must have a non-null default. - Function parameters are variables too, by name or as `$1`, `$2`, and can be qualified with the function's name when a column has the same name. ## %TYPE and %ROWTYPE: borrowing types from the schema | Declaration | Gives the variable | Typical use | |---|---|---| | `v_bal loyalty_member.points_balance%TYPE;` | the type of one column | a value read from or written to that column | | `v_copy v_bal%TYPE;` | the type of another variable | helper variables that must match | | `v_member loyalty_member%ROWTYPE;` | the table's whole row (composite) type | `SELECT * INTO v_member`, then `v_member.status` | | `v_rec record;` | whatever shape is assigned to it | rows of varying structure, loop targets | With `%TYPE` you do not hard-code `bigint` for a points balance: if the column's type later changes, you **might not need to edit the function**. It also helps polymorphic functions, where a variable can take the type of an argument. `%ROWTYPE` declares a **row variable** whose fields are read with dot notation. Because every PostgreSQL table and view has a composite type of the same name, `v_member loyalty_member;` means the same thing, but the `%ROWTYPE` spelling is the more portable one. A row variable used as a `SELECT INTO` target must match the query's columns in number and type, whereas a `record` variable adapts to whatever the query returns. `%TYPE` is also accepted in a `CREATE FUNCTION` argument list, but there it is resolved once, when the function is created, and the server prints a notice saying which type it was converted to. ## BEGIN is not a transaction The classic beginner error is reading PL/pgSQL's `BEGIN` as SQL's `BEGIN`, which starts a transaction. In PL/pgSQL, `BEGIN` and `END` **only group statements**: a function always runs inside its caller's transaction and cannot commit it. Adding an `EXCEPTION` section changes one thing: the block then acts as a **subtransaction**, whose database changes are rolled back if a handler catches an error, at a real cost on entry and exit. Committing from server code is possible only in a procedure invoked with `CALL` and in a `DO` block run outside a transaction block; procedures belong with function and procedure definitions. ## DO: a block without a function `DO $$ ... $$;` runs an **anonymous code block**. PostgreSQL treats it as the body of a function with no parameters returning `void`; it is parsed and executed a single time, and the language defaults to `plpgsql`. Use it for one-off maintenance, such as back-filling a loyalty tier for existing members, when creating and dropping a function would be noise. It cannot take arguments or return rows; it reports through `RAISE NOTICE` or by writing to tables. A `DO` executed in its own transaction may issue `COMMIT` or `ROLLBACK`; one executed inside a transaction block may not. ## Beginner mistakes worth naming 1. A semicolon straight after `BEGIN`, which is a syntax error. 2. Expecting `BEGIN ... END` to commit or roll back on its own. 3. Forgetting that an undefaulted variable is NULL, and that an `IF` whose condition is NULL takes the false branch. 4. Hard-coding column types instead of `%TYPE`, then chasing mismatches after a schema change.

  • Why does a variable declared in a block nested inside a LOOP lose its value on every iteration?
    PL/pgSQL evaluates a block's declarations, defaults included, each time control enters the block. A nested `BEGIN ... END` inside a `LOOP` is entered once per iteration, so its variables are re-initialised to their defaults, or to NULL. A running total that must survive across iterations belongs in the enclosing block.
  • When would you declare a record variable instead of using %ROWTYPE?
    When the shape is not fixed in advance: a `FOR rec IN SELECT ... LOOP` over a join of several tables, or `EXECUTE ... INTO` a dynamically built query. A `record` takes the structure of whatever row is assigned to it, while a `%ROWTYPE` variable must match its table's columns in number and type.

  • PL/pgSQL BEGIN starts a transaction and the matching END commits it.
  • Uninitialised PL/pgSQL variables default to zero or an empty string.
  • A DEFAULT expression is evaluated once per function call, not on each block entry.
  • %ROWTYPE works only on tables, so a view's rows need a record variable.
  • A DO block can take parameters and return a result set like a function.