Thread: drop function all - ?
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.
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
>
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.
this seems like an unlikely solution since i don't think it is in any
sql spec... in the mean time you could create a function that loops
through pg_proc and drops functions based on the function attributes.
check the docs for info on data in pg_proc if you do. btw, the error
messages in 7.4 are improved to help with this case.
Robert Treat
On Mon, 2003-07-14 at 05:48, Együd Csaba wrote:
> 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
> > >
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Hi Robert,
I admit your opinion. But I thought it could be a kind of service just for
convenience.
On the other hand there are a lot of commands in the PostgreSQL language
which does not appear in the original SQL standard.
Despite I think almost everybody find them very useful. So I would not say
it is an unlikely solution but a possible extension of SQL (at least
PostgreSQL).
Anyway I must say that I'll get by with or without this feature and even if
it won't be the part of the future releses I'll continue using Postgres. I
just wanted to indicate a possible customer requirement which could make it
more popular.
Best Regards,
-- Csaba
----- Original Message -----
From: "Robert Treat" <xzilla@users.sourceforge.net>
To: "Együd Csaba" <csegyud@freemail.hu>
Cc: <elein@varlena.com>; <pgsql-general@postgresql.org>
Sent: Monday, July 14, 2003 10:53 PM
Subject: Re: [GENERAL] drop function all - ?
this seems like an unlikely solution since i don't think it is in any
sql spec... in the mean time you could create a function that loops
through pg_proc and drops functions based on the function attributes.
check the docs for info on data in pg_proc if you do. btw, the error
messages in 7.4 are improved to help with this case.
Robert Treat
On Mon, 2003-07-14 at 05:48, Együd Csaba wrote:
> 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
> > >
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
---
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.