Search Postgresql Archives

RE: How to remove elements from array .

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

 

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux