How to remove elements from array .

Started by Brahmam Eswarover 7 years ago6 messages

brahmam1234@gmail.com

over 7 years ago

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.

Charles Clavadetscher

clavadetscher@swisspug.org

over 7 years ago

In reply to: Brahmam Eswar (#1)

RE: How to remove elements from array .

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

Pavel Stehule

pavel.stehule@gmail.com

over 7 years ago

In reply to: Brahmam Eswar (#1)

Re: How to remove elements from array .

2018-07-06 9:49 GMT+02:00 Brahmam Eswar <brahmam1234@gmail.com>:

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 .

In this case, unnesting can be solution

postgres=# select * from foo;
+----+----+
| c1 | c2 |
+----+----+
| t | 10 |
| f | 20 |
| f | 20 |
+----+----+
(3 rows)

postgres=# do $$
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

Regards

Pavel

Show quoted text

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.

Pavel Stehule

pavel.stehule@gmail.com

over 7 years ago

In reply to: Charles Clavadetscher (#2)

Re: How to remove elements from array .

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

Show quoted text

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

Brahmam Eswar

brahmam1234@gmail.com

over 7 years ago

In reply to: Pavel Stehule (#4)

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.

Pavel Stehule

pavel.stehule@gmail.com

over 7 years ago

In reply to: Brahmam Eswar (#5)

Re: How to remove elements from array .

2018-07-06 11:45 GMT+02:00 Brahmam Eswar <brahmam1234@gmail.com>:

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?

sure - it is relation like any other.

Can you send test case?

Show quoted text

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.