Thread: Update table using array

Hi,

I want to update a column matching all elements of array. But,I don't
want to iterate over all elements of array.
Basically, I am writing a procedure for

update tx set delete_flag=''true'' where tx_id in (10,20,);

Something like below procedure(which is not currect)

CREATE OR REPLACE FUNCTION t1(bigint[]) RETURNS text AS '
DECLARE
        cnt INTEGER;
         ret text;BEGIN
update tx set delete_flag=''true'' where tx_id in ($1);
return ret;
end;
'
language 'plpgsql';


Is there anyway to update the table for all elements of array in one go?


- Abhinav

Hi,

If it is a single-row array I would go with temporary table and a
cursor with FOR UPDATE. Check here for details:
http://www.postgresql.org/docs/current/static/sql-declare.html

2010/9/28 Mehrotra, Abhinav (GE Healthcare) <Abhinav.Mehrotra@ge.com>:
>
> Hi,
>
> I want to update a column matching all elements of array. But,I don't
> want to iterate over all elements of array.
> Basically, I am writing a procedure for
>
> update tx set delete_flag=''true'' where tx_id in (10,20,);
>
> Something like below procedure(which is not currect)
>
> CREATE OR REPLACE FUNCTION t1(bigint[]) RETURNS text AS '
> DECLARE
>        cnt INTEGER;
>         ret text;BEGIN
> update tx set delete_flag=''true'' where tx_id in ($1);
> return ret;
> end;
> '
> language 'plpgsql';
>
>
> Is there anyway to update the table for all elements of array in one go?
>
>
> - Abhinav
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>



--
Łukasz Brodziak
II MU Bioinformatyka