Hi From: Brahmam Eswar [mailto:brahmam1234@gmail.com] Sent: Freitag, 6. Juli 2018 09:50To: pgsql-general <pgsql-general@postgresql.org>; pgsql-hackers@postgresql.orgSubject: 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 20N 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:
Hi
From: Brahmam Eswar [mailto:brahmam1234@gmail.com] Sent: Freitag, 6. Juli 2018 09:50To: pgsql-general <pgsql-general@postgresql.org>; pgsql-hackers@postgresql.orgSubject: 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;
--
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);
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 tUSING (SELECT * FROM tst WHERE col1 = 'Y') AS xWHERE t.col2 = x.col2; SELECT * FROM tst; col1 | col2------+------ N | 20(1 row) RegardsCharles
INSERT INTO tst VALUES ('Y', 10), ('N', 20), ('N', 10);
SELECT * FROM tst;
col1 | col2
------+------ Y | 10 N | 20 N | 10
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;
------+------
(1 row)
RegardsCharles
Charles
pgsql-general by date:
Соглашаюсь с условиями обработки персональных данных