Search Postgresql Archives

Re: Alter Columns with Triggers

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux