RE: How to remove elements from array .

Hi

From: Brahmam Eswar [mailto:brahmam1234(at)gmail(dot)com]
Sent: Freitag, 6. Juli 2018 09:50
To: pgsql-general <pgsql-general(at)postgresql(dot)org>; pgsql-hackers(at)postgresql(dot)org
Subject: How to remove elements from array .

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 .

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.

I am not so in clear why you are using arrays in a function for that.

A solution with SQL would be:

CREATE TABLE tst (

col1 text,

col2 integer

);

INSERT INTO tst VALUES ('Y', 10), ('N', 20), ('N', 10);

SELECT * FROM tst;

col1 | col2

------+------

Y | 10

N | 20

N | 10

(3 rows)

DELETE FROM tst t

USING (SELECT * FROM tst

WHERE col1 = 'Y') AS x

WHERE t.col2 = x.col2;

SELECT * FROM tst;

col1 | col2

------+------

N | 20

(1 row)

Regards

Charles