Thread: cleaning up useless pl/pgsql functions

Hi
Do you have any easy script to remove all pl/pgsql function? After a lot of 
changes inside "create or replace function..." scripts I have a big mess. I 
want to remove all user defined pl/pgsql functions and restore some of them 
from my scripts again.


Regards,
Tomasz Myrta

Dnia 2003-06-30 23:13, Użytkownik Tomasz Myrta napisał:
> Hi
> Do you have any easy script to remove all pl/pgsql function? After a lot 
> of changes inside "create or replace function..." scripts I have a big 
> mess. I want to remove all user defined pl/pgsql functions and restore 
> some of them from my scripts again.
> 
> 
> Regards,
> Tomasz Myrta

Hmm Answer to myself:

DELETE from pg_proc where prolang =(select oid from pg_language where 
lanname='plpgsq');

What do you think about it?

Tomasz

Tomasz,

> Hmm Answer to myself:
>
> DELETE from pg_proc where prolang =(select oid from pg_language where
> lanname='plpgsq');
>
> What do you think about it?

You probably also want to put a "proowner = {your userid}" condition in there,
just in case there are any builtins/contrib stuff that uses plpgsql.

--
-Josh BerkusAglio Database SolutionsSan Francisco

"Tomasz Myrta" <jasiek@klaster.net> wrote:
> Hi
> Do you have any easy script to remove all pl/pgsql function? After a lot
of
> changes inside "create or replace function..." scripts I have a big mess.
I
> want to remove all user defined pl/pgsql functions and restore some of
them
> from my scripts again.

You shall be able to identify the name of your own function and do:

SELECT 'DROP function ' || proname || ' ('||  oidvectortypes(proargtypes) ||
');' from pg_proc WHERE proname ~ '^sp_';

I identify my own function because the prefix sp_.


I hope that this help you.

Gaetano

On Mon, 2003-06-30 at 21:13, Tomasz Myrta wrote:
> Hi
> Do you have any easy script to remove all pl/pgsql function? After a lot of
> changes inside "create or replace function..." scripts I have a big mess. I
> want to remove all user defined pl/pgsql functions and restore some of them
> from my scripts again.

DROP PROCEDURAL LANGUAGE plpgsql CASCADE;
CREATE PROCEDURAL LANGUAGE plpgsql ...

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc