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

Search Discussions

  • Lukasz Brodziak at Sep 28, 2010 at 6:50 am
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedSep 28, '10 at 6:41a
activeSep 28, '10 at 6:50a
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase