Re: How to remove elements from array .

2018-07-06 11:45 GMT+02:00 Brahmam Eswar <brahmam1234(at)gmail(dot)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?

> 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.
>