Обсуждение: Alter Columns with Triggers
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
On 12/07/2011 9:23 AM, Adrian Parker wrote: > 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. > I don't know about "quick and easy", but you can get the trigger function sources from pg_catalog using your Python program. The argument types and function names are also there, which gives you enough information to programmatically generate CREATE and DROP statements for the functions, as well as ALTER TABLE functions to add and remove the triggers. As for dependencies: doesn't PL/PgSQL only check the existence of functions called, etc, once a procedure is actually executed? I can't remember for sure, but it might not prove to be an issue. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/
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 >