Re: How to remove elements from array .

Hi All,

My request is simple,

Just browse the results from a table into an array and loop through array results to find out to unnecessary records and delete them based on certain business conditions and print the rest of the records.

Below are the array results from table. 

 {"(20310,https://google.com,AP,BR,,Y)","(20310,https://google.com,AP,,,N)","(20311,https://google.com,AP,,,N)"}

Tried to apply the Unnest on array results but giving an error  at "https://" .

Can we iterate over unnest records?

On Fri, Jul 6, 2018 at 1:56 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

2018-07-06 10:19 GMT+02:00 Charles Clavadetscher <clavadetscher@swisspug.org>:

Hi

 

From: Brahmam Eswar [mailto:brahmam1234@gmail.com]
Sent: Freitag, 6. Juli 2018 09:50
To: pgsql-general <pgsql-general@postgresql.org>; pgsql-hackers@postgresql.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:

I don't understand to the request too.

 

 

CREATE TABLE tst (

  col1 text,

  col2 integer

);

 

Attention - temp table are expensive in Postgres (mainly for higher load), so what can be done simply with arrays should be done with arrays.

Regards

Pavel

 

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

--

Thanks & Regards,
Brahmeswara Rao J.