Re: How to remove elements from array .

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
>