Re: Extracting data from deprecated MONEY fields - Mailing list pgsql-general

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;