Re: How to remove elements from array .

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

2018-07-06 9:49 GMT+02:00 Brahmam Eswar <brahmam1234@xxxxxxxxx>:

Hi ,

I tried to use array_remove to remove elements from an array but it's saying function doesn't exist . I'm able to use other array functions.

1) Capture the results with multiple columns into array . 

2)  if ay results exist then loop through an array to find out the record with col1='Y'

3)  If col1='Y' then get the respective value of Col2 (10) and delete the similar records of col2 if exist.

Col1    Col2 

 Y         10

 N         20

N          10 

Need to delete record1 and record3.To delete the array records i'm using array_remove but it says doesn't exist.

Version pgadmin4 .

In this case, unnesting can be solution

postgres=# select * from foo;
+----+----+
| c1 | c2 |
+----+----+
| t  | 10 |
| f  | 20 |
| f  | 20 |
+----+----+
(3 rows)

postgres=# do $$
declare a foo[] default array(select foo from foo);
begin
  a := array(select (c1,c2)::foo from unnest(a) g(c1,c2) where g.c1 = true);
  raise notice 'a=%', a;
end;
$$;
NOTICE:  a={"(t,10)"}
DO

Regards

Pavel

 

Snippet :-

CREATE or REPLACE FUNCTION FUNC1

(

<< List of elements >>

) AS $$

DECLARE

    

TEST_CODES record1 ARRAY;

     TEMP_REF_VALUE VARCHAR(4000);

  

BEGIN

        IS_VALID := 'S';

    

      SELECT ARRAY 

   (SELECT ROW(Col1,Col2,COl3,Col4) ::record1

    FROM table1  INTO TEST_CODES

IF array_length(TEST_CODES, 1) > 0 THEN

FOR indx IN array_lower(TEST_CODES, 1)..array_upper(TEST_CODES, 1) LOOP

     IF TEST_CODES[indx].COL1 = 'Y' THEN

TEMP_REF_VALUE:=TEST_CODES[indx].Col2;

TEST_CODES := array_remove(TEST_CODES,TEMP_REF_VALUE);

END IF;

   END Loop;

        END IF; 

--

Thanks & Regards,
Brahmeswara Rao J.