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