Обсуждение: 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