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