Add element to empty array

Question

In plpgSql, I need to add number to empty numeric array, this empty numeric array is variable.

What I do is this:

DECLARE
    new_arr INTEGER[];
BEGIN
    SELECT array_append(new_arr, 4) INTO new_arr;

This works, but I am not sure, may be exists better way for this?

Answer 1

You can do it, but this style is little bit obscure

use a assign statement, but you don't need a forget a correct initialization. In your example a new_arr doesn't hold a empty array. It holds a NULL. Do:

DECLARE new_arr int[] DEFAULT '{}';
BEGIN
  new_arr := new_arr || 4;
  -- or
  new_arr := array_append(new_arr, 4);

  -- both are equal

SELECT INTO should be used if you do query to some relation.