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(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> 2018-07-06 10:19 GMT+02:00 Charles Clavadetscher <
> clavadetscher(at)swisspug(dot)org>:
>
>> Hi
>>
>>
>>
>> *From:* Brahmam Eswar [mailto:brahmam1234(at)gmail(dot)com]
>> *Sent:* Freitag, 6. Juli 2018 09:50
>> *To:* pgsql-general <pgsql-general(at)postgresql(dot)org>;
>> pgsql-hackers(at)postgresql(dot)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.