Re: Alter Columns with Triggers

I don't recall any problems with functions or triggers, in my experience
the problems are with the views.

Any views that are on the table must be recreated.

In any case, I wrote a function a while back that can be fairly easily
modified to do what you want.

My function is for modifying a view that has other views referencing it.
It copies the definition of the views to a variable then does drop
cascade and recreates all the dependent views.

See http://archives.postgresql.org/pgsql-general/2005-02/msg01044.php

If triggers really are an issue, this query will give you the names and
finctions of all triggers on any table you want.

You can then copy the trigger and function definition, erase them and
recreate them.

select b.relname,a.tgname,c.proname from pg_trigger a join pg_class b on
b.oid=tgrelid

join pg_proc c on c.oid=tgfoid

Sim

On 07/12/2011 04:23 AM, Adrian Parker wrote:

> Hello. I'm quite new to Postgres. I've just been assigned the task of
> changing 300+ Double Precision columns, spread out over 30+ tables, to
> be of type Numeric(100, 3). Many of the columns have one or more
> triggers/functions on them.
>
> The alterations will run from a python program acting as a database
> migrator, and access to the database will be disabled while the
> migrator runs. I can progmatically query for all columns of type
> double precision, and run Alter statements against them, but how do I
> deal with Triggers/Functions? It seems I need to remove both the
> trigger and function, and re-add them after the table is altered. Some
> functions refer to others though.
>
> None of the columns to be changed are primary or foreign keys.
>
> Ideally I'd like a query that allows me to alter all the column types
> which I need to alter despite the triggers/functions in place, but I
> suppose this is not possible.
>
> Is there a quick and easy fix? Currently I'm copying/pasting the
> drop/create statements from pgadmin (its very slow and error prone)
> and putting them in my python source.
>
>
> Adrian
>