cleaning up useless pl/pgsql functions

Lists: pgsql-sql
From: Tomasz Myrta <jasiek(at)klaster(dot)net>
To: PGSQL-SQL <pgsql-sql(at)postgresql(dot)org>
Subject: cleaning up useless pl/pgsql functions
Date: 2003-06-30 21:13:28
Message-ID: 3F00A7F8.80107@klaster.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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

From: Tomasz Myrta <jasiek(at)klaster(dot)net>
To: Tomasz Myrta <jasiek(at)klaster(dot)net>
Cc: PGSQL-SQL <pgsql-sql(at)postgresql(dot)org>
Subject: Re: cleaning up useless pl/pgsql functions
Date: 2003-06-30 21:32:08
Message-ID: 3F00AC58.5070905@klaster.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Tomasz Myrta <jasiek(at)klaster(dot)net>
Cc: PGSQL-SQL <pgsql-sql(at)postgresql(dot)org>
Subject: Re: cleaning up useless pl/pgsql functions
Date: 2003-06-30 22:02:24
Message-ID: 200306301502.24119.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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 Berkus
Aglio Database Solutions
San Francisco

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: Tomasz Myrta <jasiek(at)klaster(dot)net>
Cc: PGSQL-SQL <pgsql-sql(at)postgresql(dot)org>
Subject: Re: cleaning up useless pl/pgsql functions
Date: 2003-07-01 00:39:06
Message-ID: 1057019945.28657.103.camel@jester
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

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(at)rbt(dot)ca>

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

From: "Mendola Gaetano" <mendola(at)bigfoot(dot)com>
To: "Tomasz Myrta" <jasiek(at)klaster(dot)net>, "PGSQL-SQL" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: cleaning up useless pl/pgsql functions
Date: 2003-07-01 03:40:03
Message-ID: 002401c33f82$75081c30$10d4a8c0@mm.eutelsat.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-sql

"Tomasz Myrta" <jasiek(at)klaster(dot)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