Hi 2018-07-06 10:19 GMT+02:00 Charles Clavadetscher <clavadetsc...@swisspug.org >:
> Hi > > > > *From:* Brahmam Eswar [mailto:brahmam1...@gmail.com] > *Sent:* Freitag, 6. Juli 2018 09:50 > *To:* pgsql-general <pgsql-gene...@postgresql.org>; > pgsql-hack...@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 >
