Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2CKYcx64869 for ; Mon, 12 Mar 2001 15:34:38 -0500 (EST) (envelope-from pgsql-admin-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f28KUFx76093 for ; Thu, 8 Mar 2001 15:30:15 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f28K9Gq15640 for pgsql-admin@postgresql.org; Thu, 8 Mar 2001 15:09:16 -0500 (EST) (envelope-from news) From: "Thomas F. O'Connell" X-Newsgroups: comp.databases.postgresql.admin Subject: Re: how to reload a function Date: Thu, 08 Mar 2001 14:09:13 -0600 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 52 Message-ID: <3AA7E6E9.508@monsterlabs.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org User-Agent: Mozilla/5.0 (Macintosh; N; PPC; en-US; 0.8) Gecko/20010215 X-Accept-Language: en To: pgsql-admin@postgresql.org X-Archive-Number: 200103/84 X-Sequence-Number: 930 > If I modify function A (drop && re_create), then I have to re_create > function B though no change to function B. > > Is there any way (sql stmt) let me re_load function B's defination > without using drop and create?? i have not figured out a way to do anything like this. an additional frustration is the postgres documentation's suggestion as a solution to their not having implemented ALTER TABLE DROP COLUMN to do the following (using an example table "distributors"): CREATE TABLE temp AS SELECT did, city FROM distributors; DROP TABLE distributors; CREATE TABLE distributors ( did DECIMAL(3) DEFAULT 1, name VARCHAR(40) NOT NULL ); INSERT INTO distributors SELECT * FROM temp; DROP TABLE temp; unfortunately, this doesn't restore any triggers on the table and causes the function manager to complain the next time any functions referencing this table are run. my solution has been to write an extensive perl library with the following functions: regenerate_table regenerate_function regenerate_trigger regenerate_view each of these has the intelligence to regenerate any of the dependent parts as necessary. for instance, if i were to use regenerate_function( A ) from your example, my library would recognize that it needed also to regenerate_function( B ). unfortunately, in order to do this, i have found that i pretty much need to keep my database schema on disk in the form of text files since i edit tables, functions, triggers, and views so frequently. i know this doesn't really answer your question, but i'm letting you know of the workaround i came up with in the hope that anyone else who sees this might have an even better alternative. the short answer to your question is "no", as far as i can tell. -tfo