Обсуждение: Delete function

Hi all,
How can I delete all function that match some name rules?
Ex:
I want delete all functions that matches  sp_*
and all views that matches v_*



Ciao
Gaetano

--
#exclude <windows>
#include <CSRSS>
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");

I did this but I don't know if is the correct way:

DELETE FROM pg_proc WHERE proname ~ '^sp_';
DELETE FROM pg_class WHERE relname ~ '^v_';

Ciao Gaetano.




----- Original Message -----
From: "Gaetano Mendola" <mendola@bigfoot.com>
To: <pgsql-admin@postgresql.org>
Sent: Tuesday, May 07, 2002 9:28 AM
Subject: [ADMIN] Delete function


> Hi all,
> How can I delete all function that match some name rules?
> Ex:
> I want delete all functions that matches  sp_*
> and all views that matches v_*
>
>
>
> Ciao
> Gaetano
>
> --
> #exclude <windows>
> #include <CSRSS>
> printf("\t\t\b\b\b\b\b\b");.
> printf("\t\t\b\b\b\b\b\b");
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Tuesday 07 May 2002 03:47 pm, Gaetano Mendola wrote:
> I did this but I don't know if is the correct way:
>
> DELETE FROM pg_proc WHERE proname ~ '^sp_';
> DELETE FROM pg_class WHERE relname ~ '^v_';

hmm..

i dont think its safe to fiddle with the system catalogs like that.

There exists SQL commands for that  like
 DROP FUNCTION <function_name>
or  DROP VIEW <view_name>

of course there is a limitation(read advantage) that they do not understand
pattern matches

but it is easy to overcome it using SQL to create a batch of SQL commands.

eg
regression=> \o command.sql
regression=> select 'DROP function  ' || proname  from  pg_proc WHERE proname
~ '^sp_';

then psql -f  command.sql

regds
mallah.





>
> Ciao Gaetano.
>
>
>
>
> ----- Original Message -----
> From: "Gaetano Mendola" <mendola@bigfoot.com>
> To: <pgsql-admin@postgresql.org>
> Sent: Tuesday, May 07, 2002 9:28 AM
> Subject: [ADMIN] Delete function
>
> > Hi all,
> > How can I delete all function that match some name rules?
> > Ex:
> > I want delete all functions that matches  sp_*
> > and all views that matches v_*
> >
> >
> >
> > Ciao
> > Gaetano
> >
> > --
> > #exclude <windows>
> > #include <CSRSS>
> > printf("\t\t\b\b\b\b\b\b");.
> > printf("\t\t\b\b\b\b\b\b");
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

"Rajesh Kumar Mallah." <mallah@trade-india.com> wrote:
> eg
> regression=> \o command.sql
> regression=> select 'DROP function  ' || proname  from  pg_proc WHERE
proname
> ~ '^sp_';
>
> then psql -f  command.sql

This work fine with the views, unfortunately when you drop a function you
should specify the argument type, so I did in this way (I think is the clean
way):

$ psql -t -c "SELECT 'DROP function ' || proname || ' ('||
oidvectortypes(proargtypes) || ');' from pg_proc WHERE proname ~ '^sp_'" >
todelete.sql
$ psql -f todelete.sql


Ciao
Gaetano

Thanks Mendola ,
that was a nice idea for functions....
regds
mallah.


On Tuesday 07 May 2002 06:41 pm, Gaetano Mendola wrote:
> "Rajesh Kumar Mallah." <mallah@trade-india.com> wrote:
> > eg
> > regression=> \o command.sql
> > regression=> select 'DROP function  ' || proname  from  pg_proc WHERE
>
> proname
>
> > ~ '^sp_';
> >
> > then psql -f  command.sql
>
> This work fine with the views, unfortunately when you drop a function you
> should specify the argument type, so I did in this way (I think is the
> clean way):
>
> $ psql -t -c "SELECT 'DROP function ' || proname || ' ('||
> oidvectortypes(proargtypes) || ');' from pg_proc WHERE proname ~ '^sp_'" >
> todelete.sql
> $ psql -f todelete.sql
>
>
> Ciao
> Gaetano