Record variables

Package-scoped record types

A record type defined inside a PL/SQL block is a local type, available only within that block. A record type is stored in the database only if it is declared in a package. To share a record type across multiple blocks, declare it in a package:

CREATE PACKAGE pkg AS
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
END;

CREATE PACKAGE BODY pkg AS
END;

DECLARE
  r pkg.r_type := pkg.r_type(1, 'a');
BEGIN
  RAISE NOTICE 'id: %, name: %', r.id, r.name;
END;

Output:

NOTICE:  id: 1, name: a

Using %ROWTYPE

table%ROWTYPE captures the complete row definition. Use a cursor or view to capture a partial row:

CREATE TABLE test(id INT, name VARCHAR(10));

DECLARE
  r1 test%ROWTYPE;             -- Full row: id and name columns
  CURSOR cur IS SELECT id FROM test;
  r2 cur%ROWTYPE;              -- Partial row: id column only
BEGIN
  r1.id := 1;
  r1.name := 'a';
  r2.id := 2;
  RAISE NOTICE 'r1.id: %, r1.name: %', r1.id, r1.name;
  RAISE NOTICE 'r2.id: %', r2.id;
END;

Output:

NOTICE:  r1.id: 1, r1.name: a
NOTICE:  r2.id: 2

When using %ROWTYPE or %TYPE, the record variable's default value is NULL. Table constraints are not inherited — only field names and types are copied. Hidden columns and the rowid column are excluded.

Field-by-field assignment

Assign a value to each field individually:

DECLARE
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
  r r_type;
BEGIN
  r.id := 1;
  r.name := 'name';
  RAISE NOTICE 'r.id: %, r.name: %', r.id, r.name;
END;

Output:

NOTICE:  r.id: 1, r.name: name

Assign one record variable to another

You can directly assign the values of one record variable to those of another record variable if the attributes of their corresponding fields allow implicit type conversions.

DECLARE
  TYPE r_type1 IS RECORD(id INT, name VARCHAR(10));
  TYPE r_type2 IS RECORD(id VARCHAR(10), name INT);
  r1 r_type1;
  r2 r_type2 := r_type2('1', 2);
BEGIN
  r1 := r2;  -- Implicit conversion: VARCHAR '1' -> INT, INT 2 -> VARCHAR
  RAISE NOTICE 'r.id: %, r.name: %', r1.id, r1.name;
END;

Output:

NOTICE:  r.id: 1, r.name: 2

If a conversion fails, an error is returned:

ERROR:  invalid input syntax for type integer: "a"

Assign from a query result

Use SELECT INTO to populate a record variable from a table. Include LIMIT 1 to ensure at most one row is returned:

CREATE TABLE test(id INT, name VARCHAR(10));
INSERT INTO test VALUES(1, 'a');
INSERT INTO test VALUES(2, 'b');

DECLARE
  TYPE r_type IS RECORD(id INT, name TEXT);
  r r_type;
BEGIN
  SELECT * INTO r FROM test LIMIT 1;
  RAISE NOTICE '%', r;
END;

Output:

NOTICE:  (1,a)

If the query returns more than one row without a LIMIT, an error is thrown:

ERROR:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.

FETCH INTO and UPDATE/INSERT/DELETE RETURNING INTO follow the same rules as SELECT INTO.

NULL assignment

Set a record variable to NULL only when it has no NOT NULL constraint. Setting a field that has a NOT NULL constraint to NULL raises a runtime error.

Insert a row using a record variable

CREATE TABLE test(id INT, name VARCHAR(10));

DECLARE
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
  r r_type := r_type(1, 'a');
BEGIN
  INSERT INTO test VALUES r;
END;

SELECT id, name FROM test;

Output:

 id | name
----+------
  1 | a
(1 row)

Update a row using a record variable

Use SET ROW = record_variable to update an entire row:

DECLARE
  TYPE r_type IS RECORD(id INT, name VARCHAR(10));
  r r_type := r_type(2, 'b');
BEGIN
  UPDATE test SET ROW = r WHERE id = 1;
END;

SELECT id, name FROM test;

Output:

 id | name
----+------
  2 | b
(1 row)

Where record variables can be used

Record variables are valid only in these positions:

• The right side of the SET clause in an UPDATE statement

• The VALUES clause of an INSERT statement

• The INTO clause of a RETURNING clause

Record variables cannot appear in:

• SELECT lists

• WHERE clauses

• GROUP BY clauses

• ORDER BY clauses

ROW keyword constraints

• ROW can appear only on the left side of a SET clause: SET ROW = record_variable

• ROW cannot be used with subqueries

• An UPDATE statement using ROW can have only one SET clause

INSERT and RETURNING INTO constraints

• If the VALUES clause of an INSERT statement contains a record variable, no other variables or values can appear in the same clause

• If the INTO clause of a RETURNING clause contains a record variable, no other variables or values can appear in the same clause