Thread: How to remove elements from array .
Brahmeswara Rao J.
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:
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
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 Col2Y 10N 20N 10Need to delete record1 and record3.To delete the array records i'm using array_remove but it says doesn't exist.Version pgadmin4 .
+----+----+
| c1 | c2 |
+----+----+
| t | 10 |
| f | 20 |
| f | 20 |
+----+----+
(3 rows)
declare a foo[] default array(select foo from foo);
begin
a := array(select (c1,c2)::foo from unnest(a) g(c1,c2) where g.c1 = true);
raise notice 'a=%', a;
end;
$$;
NOTICE: a={"(t,10)"}
DO
Snippet :-CREATE or REPLACE FUNCTION FUNC1(<< List of elements >>) AS $$DECLARETEST_CODES record1 ARRAY;TEMP_REF_VALUE VARCHAR(4000);BEGINIS_VALID := 'S';SELECT ARRAY(SELECT ROW(Col1,Col2,COl3,Col4) ::record1FROM table1 INTO TEST_CODESIF array_length(TEST_CODES, 1) > 0 THENFOR indx IN array_lower(TEST_CODES, 1)..array_upper(TEST_CODES, 1) LOOPIF TEST_CODES[indx].COL1 = 'Y' THENTEMP_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.
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 Col2Y 10N 20N 10Need to delete record1 and record3.To delete the array records i'm using array_remove but it says doesn't exist.Version pgadmin4 .
+----+----+
| c1 | c2 |
+----+----+
| t | 10 |
| f | 20 |
| f | 20 |
+----+----+
(3 rows)
declare a foo[] default array(select foo from foo);
begin
a := array(select (c1,c2)::foo from unnest(a) g(c1,c2) where g.c1 = true);
raise notice 'a=%', a;
end;
$$;
NOTICE: a={"(t,10)"}
DO
Snippet :-CREATE or REPLACE FUNCTION FUNC1(<< List of elements >>) AS $$DECLARETEST_CODES record1 ARRAY;TEMP_REF_VALUE VARCHAR(4000);BEGINIS_VALID := 'S';SELECT ARRAY(SELECT ROW(Col1,Col2,COl3,Col4) ::record1FROM table1 INTO TEST_CODESIF array_length(TEST_CODES, 1) > 0 THENFOR indx IN array_lower(TEST_CODES, 1)..array_upper(TEST_CODES, 1) LOOPIF TEST_CODES[indx].COL1 = 'Y' THENTEMP_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.
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:
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
Hi2018-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[ind x].Col2;
TEST_CODES := array_remove(TEST_CODES,TEMP_R EF_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.RegardsPavelINSERT 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
Brahmeswara Rao J.
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,ht tps://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:Hi2018-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[ind x].Col2;
TEST_CODES := array_remove(TEST_CODES,TEMP_R EF_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.RegardsPavelINSERT 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.