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?
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.
RAISE NOTICE 'Array % has length of %', new_arr, array_length(new_arr, 1); prints Array {} has length of <NULL>. So does it really create an empty array, does it just rely on some other obscure reason to work, or is it a bug in array_length()?ASSERT new_arr IS NULL; asserts which means that it is not null in spite of what array_length() thinks.
CREATE OR REPLACE FUNCTION test_empty_array_length() RETURNS integer AS $$ DECLARE new_arr integer[] DEFAULT '{}'; BEGIN -- http://stackoverflow.com/questions/26755195/add-element-to-empty-array RAISE NOTICE 'Array % has length %', new_arr, array_length(new_arr, 1); return array_length(new_arr, 1); END; $$ LANGUAGE plpgsql;
new_arr := new_arr || 4is probably faster: adpgtech.blogspot.de/2014/11/assignment-beats-select-into.htmlselect. Plus I personally find:=more consistent within a procedural language than using aselectto assign a value.:=should be used there. But any assignment produce SELECT .. hidden, but SELECT. a := 1 and SELEC1 1 INTO a are same.