Re: Need help on updating an entire column with a list of values, I have. - Mailing list pgsql-general

From Rajan, Pavithra
Subject Re: Need help on updating an entire column with a list of values, I have.
Date
Msg-id BE29E09321056B4C9082D207759A692801DADC22@EXCHMB01.conedison.net
Whole thread Raw
In response to Re: Need help on updating an entire column with a list of values, I have.  ("Timo Klecker" <klecker@decoit.de>)
List pgsql-general
Hello  all - Thanks for all your inputs and Klecker's script.Slightly tweaked the script, with Bryan's help to implement it.
 

CREATE OR REPLACE FUNCTION update(numeric[])

  RETURNS void AS

$BODY$

 

declare

  data alias for $1;

  table_obj record;

  I integer;

Begin

    i:=1;         //index sarted from 1.

     for table_obj in execute select * from TABLENAME order by THE_ORDER loop

            update TABLENAME set A = substring('0' || data[i]::varchar from length(data[i]::varchar)-5) where B = table_obj.B limit 1;     // had to do this  as  datatype integer was cutting out leading zeros in entries.

        I := I + 1;

     end loop;

end;

 

$BODY$

  LANGUAGE 'plpgsql';

Select update(‘{06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0}‘)

 

Rgds

Pavithra

 

 
 
 

From: Timo Klecker [mailto:klecker@decoit.de]
Sent: Friday, March 26, 2010 10:51 AM
To: Rajan, Pavithra ; pgsql-general@postgresql.org
Subject: AW: [GENERAL] Need help on updating an entire column with a list of values, I have.

Hello,

 

you could use an plpgsql function:

 

CREATE OR REPLACE FUNCTION update(numeric[])

  RETURNS void AS

$BODY$

 

declare

  data alias for $1;

  table_obj record;

  I integer;

Begin

    i:=0;

     for table_obj in execute select * from TABLENAME order by THE_ORDER loop

         update TABLENAME set A = data[i] where B = table_obj.B limit 1;

        I := I + 1;

     end loop;

end;

 

$BODY$

  LANGUAGE 'plpgsql' IMMUTABLE STRICT

 

And call it with:

 

Select update(‘{06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0}‘)

 

PS: written code was not tested!

 

Mit freundlichen Grüßen

Timo Klecker

 

 

 

Von: Rajan, Pavithra [mailto:RAJANP@coned.com]
Gesendet: Freitag, 26. März 2010 15:17
An: Timo Klecker; pgsql-general@postgresql.org
Betreff: RE: [GENERAL] Need help on updating an entire column with a list of values, I have.

 

Hello ,

     Yes -I need to get the exact  the same result as you had listed.Thanks.

 


From: Timo Klecker [mailto:klecker@decoit.de]
Sent: Friday, March 26, 2010 10:12 AM
To: Rajan, Pavithra ; pgsql-general@postgresql.org
Subject: AW: [GENERAL] Need help on updating an entire column with a list of values, I have.

Hello,

 

what do you expect as Result? Something like this?

 

E.g.              A                 B
         (numeric)        (text)
           
           06959.0           002
       15308.0           003
       15968.0           004
       18916.0           011
       19961.0           012
       26528.0           057
       29553.0           006
       29872.0           009
       30631.0           010

 

Mit freundlichen Grüßen

Timo Klecker

 

 

Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Rajan, Pavithra
Gesendet: Freitag, 26. März 2010 14:48
An: pgsql-general@postgresql.org
Betreff: [GENERAL] Need help on updating an entire column with a list of values, I have.

 

Hello - I have this table with 90 rows, which contains 2 columns ,column A (type 'numeric') and column B(type text) . Column 'A' is filled with a constant number and column 'B' has an unique entry for each row.

E.g.              A                 B
         (numeric)        (text)
           
            15968.0           002
        15968.0           003
        15968.0           004
        15968.0           011
        15968.0           012
        15968.0           057
        15968.0           006
        15968.0           009
        15968.0           010
             ..
             .. 
I would here want to update the entire column A with a list of values that I have.( 06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0 …).How do I accomplish this? Thank you.

         
   

pgsql-general by date:

Previous
From: John Gage
Date:
Subject: Re: Does anyone use in ram postgres database?
Next
From: Merlin Moncure
Date:
Subject: Re: Solid State Drives with PG (was: in RAM DB)