Re: drop function all - ? - Mailing list pgsql-general

this seems like an unlikely solution since i don't think it is in any
sql spec... in the mean time you could create a function that loops
through pg_proc and drops functions based on the function attributes.
check the docs for info on data in pg_proc if you do. btw, the error
messages in 7.4 are improved to help with this case.

Robert Treat

On Mon, 2003-07-14 at 05:48, Együd Csaba wrote:
> Elein,
> actually I do not store my source in a cvs or anything similar.
> Yes, you are absolutally right I should but I'm allways busy and
> do not have the time to deal enought with these nice softwares.
>
> But I think my problem is not about this. I have the latest
> source code in a separate file so I can restore my functions.
>
> My question points the leak of an expression which can clean
> the database from the old or expired stored procedures.
> Actually I also can create shell scripts to delete the stored
> procs (pg_dump, grep, sed, ...) but I think this could be
> covered its own command in such a flexible DBMS like
> Postgres.
>
> Best Regards
>
> -- Csaba
>
> ----- Original Message -----
> From: "elein" <elein@varlena.com>
> To: "Együd Csaba" <csegyud@freemail.hu>
> Cc: <pgsql-general@postgresql.org>
> Sent: Sunday, July 13, 2003 10:44 PM
> Subject: Re: [GENERAL] drop function all - ?
>
>
> > You don't keep the SQL for your functions in
> > source code control?
> >
> > When working on a database design, always keep your
> > original *and* modified SQL scripts so that you can
> > always create an empty version of your production
> > system.  Part of that is creating drop scripts
> > for each object, including functions, that you create.
> > CYA.
> >
> > For those of you playing fast and loose,
> > this query will produce a list of functions and arguments
> > in the schema public.  Use it in a shell script loop
> > to drop your functions.
> >
> > SELECT
> > n.nspname || '.' || p.proname || '(' ||
> pg_catalog.oidvectortypes(p.proargtypes) || ')'
> > FROM pg_catalog.pg_proc p
> >      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
> > WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
> >       AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
> >       AND NOT p.proisagg
> >       AND n.nspname ~ '^public$';
> >
> > DO NOT under ANY circumstances drop
> > ANY functions in the schema pg_catalog.
> >
> > elein@varlena.com
> >
> >
> > On Sun, Jul 13, 2003 at 02:02:15PM +0200, Együd Csaba wrote:
> > > Hi,
> > > is there any way to get rid of all of my functions in a database. I mean
> > > such an expression: DROP FUNCTION ALL;
> > >
> > > The point of this issue is that time to time one writes and rewrites
> > > functions with the same name and forgets to remove the existing ones. At
> a
> > > certain time one tries to call a function but don't know why it fails.
> It
> > > fails bacause there is an other (or more) function with the same name
> and
> > > with a very similar parameter list (the same number and convertable
> types).
> > >
> > > I'm in this situation....
> > > So I'd like to remove all my functions and recreate the latest ones from
> a
> > > file. Without dropping the whole database of corse.
> > >
> > > Any suggestions????
> > >
> > > Thank you,
> > >
> > > -- Csaba
> > >

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL