Redshift Stored Procedure Array Variable Alternative

The ARRAY data type is a composite data value that consists of zero or more elements of a specified specific data type. Most of the relational databases such as Netezza, Teradata, etc. supports array variables to be defined inside stored procedures. As of now, Amazon Redshift does not support array variables. In this article, we will check, Redshift Stored Procedure array variable alternative.

Redshift Stored Procedure Array Variable Support

As mentioned, Amazon Redshift does not support array variables. In my other article, Amazon Redshift Array Support and Alternatives, we have discussed how to use a json function with array stored in varchar types. But, when you migrate Teradata or Oracle stored procedures to Redshift you will find it hard to identify the alternative option for array variables.

Redshift Stored Procedure Array Variable Alternative

As an alternative to array variables in Redshift stored procedure, you can build a string with a unique delimiter.

For example, you can use following code inside a loop to build a string from the cursor. It is similar to insert records into array variable.

v1 := concat(v1 , (rec.a ||'~~')) ;

Where rec.a is a column from cursor and ‘~~’ is a delimiter.

The statement will concatenate an extra delimiter at the end of the string. You can remove that by using RTRIM function.

For example, following piece of code will remove the last delimiter.

v1 := rtrim(v1, '~~');

Once the string is built, you can use the string function, for example, SPLIT_PART function to extract values using the index number.

For example, following piece of code can extract 2nd value from the string.

v2 := SPLIT_PART(v1, '~~', 2);

Count Number of Elements in Array Redshift Alternative

In most of the cases, you will loop through the some logic based on number of elements in the array. As we are using delimited string as an array, you can use regular expression functions such as REGEXP_COUNT to count number of elements in string based on a delimiter.

For example, consider following piece of code to get the number of elements in a delimited string.

item_cnt := REGEXP_COUNT(v1,'~~')+1;

Redshift Stored Procedure Array Variable Alternative Example

Now, putting all together, following is the Redshift stored procedure with string as an array variables.

CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  v1 varchar(500) := '';
  v2 varchar(500) := '';
  v3 varchar(500) := '';
  i int := 1;
  item_cnt int := 0;
BEGIN
  FOR rec IN SELECT a FROM tbl_record
  LOOP
    v1 := concat(v1 ,(rec.a ||'~~')) ;
    --RAISE INFO 'a = %', rec.a;
  END LOOP;
  v1 := rtrim(v1, '~~');
  item_cnt := REGEXP_COUNT(v1,'~~')+1;
  v2 := SPLIT_PART(v1, '~~', 2);
  RAISE INFO 'array = %', v1;
  RAISE INFO 'Items in Array = %', item_cnt;
  while i <= item_cnt
  LOOP
    v2 := SPLIT_PART(v1, '~~', i);
    RAISE INFO 'Value = %', v2;
    i := i + 1;
  end loop;  
END;
$$;

Following is the output of above stored procedure.

call record_example()
 - - - 
1.02s 2020-07-15T09:26:59.399333300Z
 - - - 
WARNING: (SQLSTATE: 01000, SQLCODE: 0): array = 1~~2~~2~~3~~2
WARNING: (SQLSTATE: 01000, SQLCODE: 0): Items in Array = 5
WARNING: (SQLSTATE: 01000, SQLCODE: 0): Value = 1
WARNING: (SQLSTATE: 01000, SQLCODE: 0): Value = 2
WARNING: (SQLSTATE: 01000, SQLCODE: 0): Value = 2
WARNING: (SQLSTATE: 01000, SQLCODE: 0): Value = 3
WARNING: (SQLSTATE: 01000, SQLCODE: 0): Value = 2

Hope this helps 🙂