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

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
> >
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.495 / Virus Database: 294 - Release Date: 2003. 06. 30.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if
your
> >       joining column's datatypes do not match
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.495 / Virus Database: 294 - Release Date: 2003. 06. 30.