Question 90559 in PostgreSQL Question posted in
The official documentation can be found here.

How can i select an array into a larger array without overwriting it in postgres? – Postgresql

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?

Tags:

2

Answers


  1. 0

    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}.

    Login or Signup to reply.
  2. 0

    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]);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search