Redshift RECORD Type, Usage and Examples

Similar to other procedural language, such as nzplsql, Redshift plpgsql supports RECORD variables. The Redshift RECORD type variable is similar to row types, but they have no predefined structure associated with it. Record type variables can be used inside Redshift stored procedures. They are used in selections or FOR loops to hold one row from a SELECT operation.

Redshift RECORD Type Variable

A RECORD in Redshift plpgsql is not a true data type, but only a placeholder. Record type variables assume the actual row structure of the row that they are assigned during a SELECT statement or looping structure such as FOR command. The substructure of a record variable can change each time it is assigned a value. Until a record variable is first assigned to, it has no substructure. Any attempt to access a field in a empty record variable throws a run-time error.

Redshift RECORD Declaration

Below is the syntax for declaring RECORD types inside stored procedures:

name RECORD;

RECORD types variable can only be assigned at the run-time.

Once the RECORD variable is declared, you can use that with SELECT query or FOR loop.

For example, you can use it in FOR loop

FOR rec IN query;

or use it with SELECT statement.

SELECT expressions INTO rec FROM ...;

Redshift RECORD Example

Below example demonstrate usage of RECORD type in SELECT statement. Example also demonstrates GET DIAGNOSTICS and NOT FOUND variable types.

CREATE OR REPLACE PROCEDURE record_type_example()
LANGUAGE plpgsql
AS $$
DECLARE 
  rec RECORD;
  rec2 RECORD;
  form_type char(255);
  integer_var integer;
BEGIN
  SELECT * 
  INTO   rec 
  FROM   test_table1 
  WHERE  col1 = 1; 
   
  form_type := rec.col1 || ' ' || rec.col2; 
  RAISE INFO 'Name: %', form_type; 
  GET DIAGNOSTICS integer_var = ROW_COUNT;
  RAISE INFO 'Row Count: %', integer_var;
  
  SELECT * 
  INTO   rec2 
  FROM   test_table1 
  WHERE  col1 = 10; 
   
  IF NOT FOUND THEN 
	RAISE EXCEPTION 'ID % not found', rec2.col1; 
  END IF;

EXCEPTION 
  WHEN others THEN 
	RAISE INFO  'ERROR: %', SQLERRM;
END;
$$;

Here is the output:

call record_type_example();

INFO:  Name: 1 AA
INFO:  Row Count: 1
INFO:  ERROR: ID <NULL> not found
ERROR:  ID <NULL> not found

Let’s check another example using record type in FOR loop:

CREATE OR REPLACE PROCEDURE record_type_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec_type RECORD;
BEGIN
  FOR rec_type IN SELECT col1 FROM test_table1
  LOOP
    RAISE INFO 'a = %', rec_type.col1;
  END LOOP;
END;
$$;

Here is the output:

call record_type_example();

INFO:  a = 1
INFO:  a = 2
INFO:  a = 3

Redshift RECORD Type Variable Names

Below are the variable names that you can use along with RECORD types variables. These variables are useful when handling exceptions within stored procedures when using record types.

FOUND – It is a Boolean variable that can be used immediately after a SELECT INTO to check whether an assignment was successful.
NOT FOUND – Used to raise an exception if a SELECT INTO statement does not match on the requested input name
ROW_COUNT – Count number of values in RECORD type variable. This variable will be used with GET DIAGNOSTICS.