How can i select an array into a larger array without overwriting it in postgres?-CodePudding

I am writing some SQL code where i have a comma separated string with 3 values. I would like to transform this string into an array and write it into the first 3 elements of a larger array.

My naive approach was this:

DO $$
DECLARE 
    my_string varchar := '1.0,1.1,1.2';
    target_array real[6] := '{0., 0., 0., 0., 0., 0.}';
BEGIN
    select string_to_array(my_string, ',') into target_array;
    raise notice '%', target_array;
END$$;

this returns NOTICE: {1,1.1,1.2}

what i want is NOTICE: {1,1.1,1.2,0.,0.,0.}

i tried to slice the target array target_array[:3] but this results in a syntax error.

So, how do i do this?

CodePudding user response：

See if this solves your problem :

CREATE OR REPLACE FUNCTION arraycalc(arrayone INT[], arraytwo INT[] )
RETURNS TEXT AS 
$body$
DECLARE 
iaReturnarray INT [] ; 
iCount INT := 1 ; 
BEGIN 

WHILE iCount <= array_length(arrayone , 1) 
LOOP 
iaReturnarray := array_append( iaReturnarray , arrayone[iCount] ); 
iCount := iCount 1 ; 
END LOOP ; 
WHILE iCount <= array_length(arraytwo , 1 )
LOOP 
iaReturnarray := array_append( iaReturnarray , arraytwo[iCount] ); 
iCount := iCount 1 ; 
END LOOP ; 

RETURN iaReturnarray::text ; 
END ; 

$body$ 
LANGUAGE plpgsql ; 


SELECT arraycalc(ARRAY[1,2,3] , ARRAY[0,0,0,0,0,0])CREATE OR REPLACE FUNCTION arraycalc(arrayone INT[], arraytwo INT[] )
RETURNS TEXT AS 
$body$
DECLARE 
iaReturnarray INT [] ; 
iCount INT := 1 ; 
BEGIN 

WHILE iCount <= array_length(arrayone , 1) 
LOOP 
iaReturnarray := array_append( iaReturnarray , arrayone[iCount] ); 
iCount := iCount 1 ; 
END LOOP ; 
WHILE iCount <= array_length(arraytwo , 1 )
LOOP 
iaReturnarray := array_append( iaReturnarray , arraytwo[iCount] ); 
iCount := iCount 1 ; 
END LOOP ; 

RETURN iaReturnarray::text ; 
END ; 

$body$ 
LANGUAGE plpgsql ; 


SELECT arraycalc(ARRAY[1,2,3] , ARRAY[0,0,0,0,0,0]);

CodePudding user response：

Concatenate my_string converted to an array (i.e. my_string_array) with a slice of target_array that has its first elements removed.

DO $$
DECLARE 
  my_string varchar := '1.0,1.1,1.2';
  target_array real[] := '{0., 0., 0., 0., 0., 0.}';
  my_string_array real[] := string_to_array(my_string, ',');
BEGIN
  target_array := my_string_array||target_array[array_length(my_string_array,1)   1:];
  raise notice '%', target_array;
end
$$;

Or you can use a loop

DO $$
DECLARE 
    my_string varchar := '1.0,1.1,1.2';
    target_array real[] := '{0., 0., 0., 0., 0., 0.}';
    my_string_array real[] := string_to_array(my_string, ',');
    i integer;
begin
    for i in 1 .. array_length(my_string_array, 1) loop
        target_array[i] := my_string_array[i];
    end loop;
    raise notice '%', target_array;
end
$$;

Both produce {1,1.1,1.2,0,0,0}.