Right you are, Tom!
In case anyone else is facing the same migration, pasted in below is a
pl/pgsql function that does the conversion.
~ Thanks to all
~ Ken
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Saturday, June 07, 2008 11:25 AM
> Subject: Re: [GENERAL] Extracting data from deprecated MONEY fields
>
...
> If you do it within plpgsql it should work. Just assign the money value
> to a text variable (or vice versa).
>
> regards, tom lane
CREATE OR REPLACE FUNCTION "public"."convert_money_column"(varchar, varchar,
varchar)
RETURNS varchar AS
$BODY$
/*
Converts the column given by arg 3 (in the table given by arg 2
in the schema given by arg 1) from a "money" type to a "numeric(10,2)"
type, and repopulates the new column with the values from the old.
Before doing that, it makes a backup of the original table,
which should be deleted manually after verifying the results.
*/
DECLARE
this_schema ALIAS FOR $1;
this_table ALIAS FOR $2;
this_column ALIAS FOR $3;
q varchar := '';
q2 varchar := '';
rec record;
this_oid oid;
this_varchar varchar := '';
this_numeric numeric(10,2);
n integer := 0;
BEGIN
q := 'CREATE TABLE ' || this_schema || '.' || this_table || '_bak'
|| ' AS SELECT * FROM ' || this_schema || '.' || this_table;
EXECUTE q;
q := 'ALTER TABLE ' || this_schema || '.' || this_table
|| ' ADD COLUMN ' || this_column || '_ money';
EXECUTE q;
q := 'UPDATE ' || this_schema || '.' || this_table
|| ' SET ' || this_column || '_ = ' || this_column;
EXECUTE q;
q := 'ALTER TABLE ' || this_schema || '.' || this_table
|| ' DROP COLUMN ' || this_column || ' CASCADE';
EXECUTE q;
q := 'ALTER TABLE ' || this_schema || '.' || this_table
|| ' ADD COLUMN ' || this_column || ' numeric(10,2)';
EXECUTE q;
q := 'SELECT oid, ' || this_column || '_ AS money_column FROM '
|| this_schema || '.' || this_table ;
FOR rec IN EXECUTE q LOOP
this_oid := rec.oid;
this_varchar := rec.money_column;
this_varchar := replace(this_varchar, '$', '');
this_varchar := replace(this_varchar, ',', '');
this_numeric := this_varchar;
q2 := 'UPDATE ' || this_schema || '.' || this_table
|| ' SET ' || this_column || ' = ' || this_numeric
|| ' WHERE oid = ' || this_oid;
EXECUTE q2;
n := n + 1;
END LOOP;
q := 'ALTER TABLE ' || this_schema || '.' || this_table
|| ' DROP COLUMN ' || this_column || '_ CASCADE';
EXECUTE q;
RETURN 'Did ' || n || ' records';
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
