Re: How to remove elements from array .

Hi

2018-07-06 9:49 GMT+02:00 Brahmam Eswar <brahmam1234(at)gmail(dot)com>:

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