Thread: Drop all overloads of a function without knowing parameter types
Hi All,
Is there any easy way to drop a function (all overloads of it) without knowing the parameter types? If not, it would be good to see it added.
When I change a function definition I just want to run the SQL script that defines it and have any existing function replaced. CREATE OR REPLACE FUNCTION does this - except if the parameter types, or even names, have changed. If this SO thread is correct, it's quite tricky to drop existing functions without knowing the parameter types:
http://stackoverflow.com/questions/7622908/drop-function-without-knowing-the-number-type-of-parameters
I'd like to see a much easier way. Either something like "DROP FUNCTION name ALL" or a system function that does the same thing or at least a built-in, "supported" way to get the signature of all the overloads, so they can be dropped one by one.
Regards,
Evan
Is there any easy way to drop a function (all overloads of it) without knowing the parameter types? If not, it would be good to see it added.
When I change a function definition I just want to run the SQL script that defines it and have any existing function replaced. CREATE OR REPLACE FUNCTION does this - except if the parameter types, or even names, have changed. If this SO thread is correct, it's quite tricky to drop existing functions without knowing the parameter types:
http://stackoverflow.com/questions/7622908/drop-function-without-knowing-the-number-type-of-parameters
I'd like to see a much easier way. Either something like "DROP FUNCTION name ALL" or a system function that does the same thing or at least a built-in, "supported" way to get the signature of all the overloads, so they can be dropped one by one.
Regards,
Evan
Evan Martin <postgresql@realityexists.net> writes:
> Is there any easy way to drop a function (all overloads of it) without
> knowing the parameter types?
Something along the lines of
do $$
declare fname text;
begin
for fname in select oid::regprocedure from pg_proc where proname = 'foo' loop
execute 'drop function ' || fname;
end loop;
end$$;
Adjust WHERE condition to taste.
> If not, it would be good to see it added.
People periodically ask for extensions flavored more or less like this,
but I'm suspicious of building any such thing into the core. There's too
little commonality in the exact conditions they want to search on.
Leaving it at the level of a scripting problem, as above, allows arbitrary
customization of the search condition.
regards, tom lane
Thanks for that "oid::regprocedure" trick! A query like this is fairly simple once you know it, but completely non-obvious when you don't. I'm not sure what conditions others want to search on (couldn't find it in the list archives), but "by qualified function name" seems like the obvious one. If you don't wish to add that to the core, how about a system function that does this and is mentioned in the documentation for DROP FUNCTION? That way, if people have other criteria they can find the function, look at its source and adapt it to their needs. If you don't want to add a function, either, it would be good to at least document this (on the DROP FUNCTION page). Something like "Note: DROP FUNCTION does not allow you to drop a function without knowing its argument types, but you can use the following script to drop all overloads of a given function name..." Regards, Evan On 03/02/2014 19:09, Tom Lane wrote: > Evan Martin <postgresql@realityexists.net> writes: >> Is there any easy way to drop a function (all overloads of it) without >> knowing the parameter types? > Something along the lines of > > do $$ > declare fname text; > begin > for fname in select oid::regprocedure from pg_proc where proname = 'foo' loop > execute 'drop function ' || fname; > end loop; > end$$; > > Adjust WHERE condition to taste. > >> If not, it would be good to see it added. > People periodically ask for extensions flavored more or less like this, > but I'm suspicious of building any such thing into the core. There's too > little commonality in the exact conditions they want to search on. > Leaving it at the level of a scripting problem, as above, allows arbitrary > customization of the search condition. > > regards, tom lane > >
On Mon, Feb 3, 2014 at 10:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > People periodically ask for extensions flavored more or less like this, > but I'm suspicious of building any such thing into the core. There's too > little commonality in the exact conditions they want to search on. > Leaving it at the level of a scripting problem, as above, allows arbitrary > customization of the search condition. I understand the POV of both Evan and you here. However, I think that there might be a good solution for this particular case - to allow dropping functions by name only if it has the only signature, but if there are 2 or more signatures then print an error specifying all the forms of the function, eg.: ERROR: Can not drop function 'foo' because it has more then one signature: foo(integer), foo(text). I am sure It would simplify life significantly. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
Sergey Konoplev escribió: > On Mon, Feb 3, 2014 at 10:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > People periodically ask for extensions flavored more or less like this, > > but I'm suspicious of building any such thing into the core. There's too > > little commonality in the exact conditions they want to search on. > > Leaving it at the level of a scripting problem, as above, allows arbitrary > > customization of the search condition. > > I understand the POV of both Evan and you here. However, I think that > there might be a good solution for this particular case - to allow > dropping functions by name only if it has the only signature, but if > there are 2 or more signatures then print an error specifying all the > forms of the function, eg.: > > ERROR: Can not drop function 'foo' because it has more then one > signature: foo(integer), foo(text). But that doesn't solve Evan's request. He would want both functions gone, not an error. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Tue, Feb 4, 2014 at 8:35 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Sergey Konoplev escribió: >> On Mon, Feb 3, 2014 at 10:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> > People periodically ask for extensions flavored more or less like this, >> > but I'm suspicious of building any such thing into the core. There's too >> > little commonality in the exact conditions they want to search on. >> > Leaving it at the level of a scripting problem, as above, allows arbitrary >> > customization of the search condition. >> >> I understand the POV of both Evan and you here. However, I think that >> there might be a good solution for this particular case - to allow >> dropping functions by name only if it has the only signature, but if >> there are 2 or more signatures then print an error specifying all the >> forms of the function, eg.: >> >> ERROR: Can not drop function 'foo' because it has more then one >> signature: foo(integer), foo(text). > > But that doesn't solve Evan's request. He would want both functions > gone, not an error. I was writing about some kind of a compromise. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
2014-02-03 Evan Martin <postgresql@realityexists.net>:
Thanks for that "oid::regprocedure" trick! A query like this is fairly simple once you know it, but completely non-obvious when you don't.
I'm not sure what conditions others want to search on (couldn't find it in the list archives), but "by qualified function name" seems like the obvious one. If you don't wish to add that to the core, how about a system function that does this and is mentioned in the documentation for DROP FUNCTION? That way, if people have other criteria they can find the function, look at its source and adapt it to their needs. If you don't want to add a function, either, it would be good to at least document this (on the DROP FUNCTION page). Something like "Note: DROP FUNCTION does not allow you to drop a function without knowing its argument types, but you can use the following script to drop all overloads of a given function name..."
The function name (as well as the name of any other database object, such as view or rule)
can have a prefix or suffix. (We are prefix our functions with the class name to consider
them as methods in terms of object-oriented design.) Thus, such system function
for dropping functions should accept something like a regular expression as
it argument.
Moreover, how about other database objects which can be dropped: views, triggers, rules,
domains etc etc. For completeness it is necessary the existence of system functions
for dropping these objects too.
So, I am with Tom here.
// Dmitry.
On Tue, Feb 4, 2014 at 8:35 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Sergey Konoplev escribió: >> On Mon, Feb 3, 2014 at 10:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> > People periodically ask for extensions flavored more or less like this, >> > but I'm suspicious of building any such thing into the core. There's too >> > little commonality in the exact conditions they want to search on. >> > Leaving it at the level of a scripting problem, as above, allows arbitrary >> > customization of the search condition. >> >> I understand the POV of both Evan and you here. However, I think that >> there might be a good solution for this particular case - to allow >> dropping functions by name only if it has the only signature, but if >> there are 2 or more signatures then print an error specifying all the >> forms of the function, eg.: >> >> ERROR: Can not drop function 'foo' because it has more then one >> signature: foo(integer), foo(text). > > But that doesn't solve Evan's request. He would want both functions > gone, not an error. Yes, I was mostly writing about the general case rather than about the OP's one. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
Sergey Konoplev <gray.ru@gmail.com> writes:
> On Tue, Feb 4, 2014 at 8:35 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> Sergey Konoplev escribi�:
>>> On Mon, Feb 3, 2014 at 10:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> People periodically ask for extensions flavored more or less like this,
>>>> but I'm suspicious of building any such thing into the core. There's too
>>>> little commonality in the exact conditions they want to search on.
>>>> Leaving it at the level of a scripting problem, as above, allows arbitrary
>>>> customization of the search condition.
>>> I understand the POV of both Evan and you here. However, I think that
>>> there might be a good solution for this particular case - to allow
>>> dropping functions by name only if it has the only signature, but if
>>> there are 2 or more signatures then print an error specifying all the
>>> forms of the function, eg.:
>>>
>>> ERROR: Can not drop function 'foo' because it has more then one
>>> signature: foo(integer), foo(text).
>> But that doesn't solve Evan's request. He would want both functions
>> gone, not an error.
> I was writing about some kind of a compromise.
My point was precisely that a compromise would satisfy nobody. There
would be a few cases for which it was Exactly The Right Thing, and many
more for which you'd still need to learn how to do the EXECUTE trick.
I wonder whether we shouldn't address this by adding a few examples
of that type of trick to the docs. Not sure where, though ...
regards, tom lane
Tom Lane-2 wrote > I wonder whether we shouldn't address this by adding a few examples > of that type of trick to the docs. Not sure where, though ... Probably the Wiki would be a better place to put this kind of material. A link to there from "21. Managing Database" would seem to be most appropriate. Adding another section to chapter III is a possibility as well. Something like: "XX. Database Scripting". It could point to the Wiki as well as cross-reference both the "System Catalogs" section and the "PL/pgSQL[...]" section with a brief overview of the kinds of activities that are not provided for by standard internal functions or SQL commands. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Drop-all-overloads-of-a-function-without-knowing-parameter-types-tp5790367p5790515.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
In a nutshell: I think the difficulty of dropping functions is inconsistent with the difficulty of dropping other objects and I'd like to see this inconsistency fixed.
So I don't agree with the suggestion of matching function names using a regex, since that's not supported for other types of objects. To explain the use case a little better:
I maintain a set of scripts that can create a database from scratch. Sometimes I also need to update an existing database to the latest version. For tables this obviously requires separate scripts to preserve data, but views, rules and triggers can be updated just by using CREATE OR REPLACE in the DB creation scripts. Functions can almost be updated this way, but not quite. Function arguments may change over time. The script that creates them doesn't know and doesn't care which old version of the function already exists, if any - it just wants to replace it.
I'm sure this is not an uncommon scenario. Current options for the user are:
1) Maintain a list of DROP IF EXISTS statements for all function signatures that ever existed.
2) Roll their own code to find any existing functions, which is not simple as the SO thread I mentioned shows. 2 users with over 20K reputation answered and nobody knew the "oid:regprocedure" trick.
3) Since yesterday: find Tom Lane's post in this list.
I'd just like to see an easy to use, reliable and easy to discover way to do this. The general "execute trick" is good to know, but a user shouldn't resort to it for something that (from the user's point of view) is as simple as DROP VIEW or DROP INDEX.
If nothing else, the manual page for DROP FUNCTION seems like a good place to document this, since that's the obvious place where anyone would look to find out how to drop a function.
Regards,
Evan
On 04/02/2014 17:48, Tom Lane wrote:
So I don't agree with the suggestion of matching function names using a regex, since that's not supported for other types of objects. To explain the use case a little better:
I maintain a set of scripts that can create a database from scratch. Sometimes I also need to update an existing database to the latest version. For tables this obviously requires separate scripts to preserve data, but views, rules and triggers can be updated just by using CREATE OR REPLACE in the DB creation scripts. Functions can almost be updated this way, but not quite. Function arguments may change over time. The script that creates them doesn't know and doesn't care which old version of the function already exists, if any - it just wants to replace it.
I'm sure this is not an uncommon scenario. Current options for the user are:
1) Maintain a list of DROP IF EXISTS statements for all function signatures that ever existed.
2) Roll their own code to find any existing functions, which is not simple as the SO thread I mentioned shows. 2 users with over 20K reputation answered and nobody knew the "oid:regprocedure" trick.
3) Since yesterday: find Tom Lane's post in this list.
I'd just like to see an easy to use, reliable and easy to discover way to do this. The general "execute trick" is good to know, but a user shouldn't resort to it for something that (from the user's point of view) is as simple as DROP VIEW or DROP INDEX.
If nothing else, the manual page for DROP FUNCTION seems like a good place to document this, since that's the obvious place where anyone would look to find out how to drop a function.
Regards,
Evan
On 04/02/2014 17:48, Tom Lane wrote:
I was writing about some kind of a compromise.My point was precisely that a compromise would satisfy nobody. There would be a few cases for which it was Exactly The Right Thing, and many more for which you'd still need to learn how to do the EXECUTE trick. I wonder whether we shouldn't address this by adding a few examples of that type of trick to the docs. Not sure where, though ... regards, tom lane
Evan Martin wrote > In a nutshell: I think the difficulty of dropping functions is > inconsistent with the difficulty of dropping other objects and I'd like > to see this inconsistency fixed. > > So I don't agree with the suggestion of matching function names using a > regex, since that's not supported for other types of objects. To explain > the use case a little better: > > I maintain a set of scripts that can create a database from scratch. > Sometimes I also need to update an existing database to the latest > version. For tables this obviously requires separate scripts to preserve > data, but views, rules and triggers can be updated just by using CREATE > OR REPLACE in the DB creation scripts. Functions can /almost/ be updated > this way, but not quite. > Function arguments may change over time. No, they cannot. If the arguments change you are dealing with an entirely new object. And often you end up keeping the old function around for backward-compatibility. The two objects have their own life-cycle that they should be in full control of and not subjected to external objects dropping them because they share the same name. For an analogy how would your scripts deal with. ALTER TABLE table1 RENAME table2; This is exactly what you are effectively doing when you go and change a function signature though there is no "RENAME" action available for functions. > The script that creates them doesn't know and doesn't care /which/ old > version of the function already exists, if any - it just wants to > replace it. > > I'm sure this is not an uncommon scenario. Current options for the user > are: > > 1) Maintain a list of DROP IF EXISTS statements for all function > signatures that ever existed. > 2) Roll their own code to find any existing functions, which is not > simple as the SO thread > <stackoverflow.com/questions/7622908/drop-function-without-knowing-the-number-type-of-parameters> > I mentioned shows. 2 users with over 20K reputation answered and nobody > knew the "oid:regprocedure" trick. > 3) Since yesterday: find Tom Lane's post in this list. > > I'd just like to see an easy to use, reliable and easy to discover way > to do this. The general "execute trick" is good to know, but a user > shouldn't resort to it for something that (from the user's point of > view) is as simple as DROP VIEW or DROP INDEX. > > If nothing else, the manual page for DROP FUNCTION seems like a good > place to document this, since that's the obvious place where anyone > would look to find out how to drop a function. > > Regards, > > Evan > > On 04/02/2014 17:48, Tom Lane wrote: >>> I was writing about some kind of a compromise. >> My point was precisely that a compromise would satisfy nobody. There >> would be a few cases for which it was Exactly The Right Thing, and many >> more for which you'd still need to learn how to do the EXECUTE trick. >> >> I wonder whether we shouldn't address this by adding a few examples >> of that type of trick to the docs. Not sure where, though ... >> >> regards, tom lane >> >> function(text, text) is not the same as function(text, integer). If you no longer are supporting function(text, text) in your current database version you should have a "DROP FUNCTION function(text, text)" command somewhere in your "upgrade routine". This is partly a learning exercise in how things are. DROP FUNCTION function; better continue to fail as not having specified a correct function name since the signature IS part of the name and defines a unique and self-contained database object. However, I'll concede that since functions are the only class of object that allow for "name overloading" providing a built-in ability to "DROP ALL FUNCTION WITH BASE NAME function" - excluding those in pg_catalog - would have value. No regular expressions just a simple name-without-args literal match. If you are doing version controlled upgrades you should not be using this function but during the R&D phase I can imagine it would come in quite handy. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Drop-all-overloads-of-a-function-without-knowing-parameter-types-tp5790367p5790522.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Sergey Konoplev escribió:
> >> I understand the POV of both Evan and you here. However, I think that
> >> there might be a good solution for this particular case - to allow
> >> dropping functions by name only if it has the only signature, but if
> >> there are 2 or more signatures then print an error specifying all the
> >> forms of the function, eg.:
> >>
> >> ERROR: Can not drop function 'foo' because it has more then one
> >> signature: foo(integer), foo(text).
> I was writing about some kind of a compromise.
I wonder if it'd work to have some way to specify wildcards for function
arguments. For instance, if you say
DROP FUNCTION foo('*');
all versions with a single argument are dropped;
DROP FUNCTION foo('*', '*');
drops all versions with two arguments. If you do
DROP FUNCTION foo('*+');
then all versions are dropped regardless of number of arguments.
Maybe some kind of keyword instead of the funny-looking operators would
work better --- not proposing that exact syntax, only the general idea.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
* David Johnston (polobo@yahoo.com) wrote:
> Evan Martin wrote
> > So I don't agree with the suggestion of matching function names using a
> > regex, since that's not supported for other types of objects. To explain
> > the use case a little better:
Uh, we could add such support, which might be very handy.
> > I maintain a set of scripts that can create a database from scratch.
> > Sometimes I also need to update an existing database to the latest
> > version. For tables this obviously requires separate scripts to preserve
> > data, but views, rules and triggers can be updated just by using CREATE
> > OR REPLACE in the DB creation scripts. Functions can /almost/ be updated
> > this way, but not quite.
> > Function arguments may change over time.
>
> No, they cannot. If the arguments change you are dealing with an entirely
> new object.
Yeah, for better or worse, we allow function overloading and we're not
going to be dropping that.
Thanks,
Stephen
Attachment
On 04/02/2014 19:56, David Johnston wrote: > No, they cannot. If the arguments change you are dealing with an entirely > new object. And often you end up keeping the old function around for > backward-compatibility. Of course, I understand that it's a different object, technically, but from the user point of view it may replace the old function. Whether it does or not depends on your upgrade strategy, but in our case it always does. I'm making an argument from the point of view of usability here, not based on the technicalities of what is the "same" object. And I also agree that users should be aware that overloaded functions are different objects. I think there is no danger of anyone missing that fact, even if support for dropping all overloads was added, because it would still require different syntax. The usual CREATE OR REPLACE syntax still wouldn't work. > For an analogy how would your scripts deal with. > > ALTER TABLE table1 RENAME table2; > They would deal with it by doing: DROP TABLE IF EXISTS table1; CREATE TABLE table2 (...); ... if it wasn't for the fact that this would lose all data in the table. For functions this is not a problem. (Of course, you could always add support for "ALTER TABLE table1 RENAME table2 IF NOT ALREADY RENAMED FROM table1" but the use case is not as strong. ;)) > However, I'll concede that since functions are the only class of object that > allow for "name overloading" providing a built-in ability to "DROP ALL > FUNCTION WITH BASE NAME function" - excluding those in pg_catalog - would > have value. No regular expressions just a simple name-without-args literal > match. > > If you are doing version controlled upgrades you should not be using this > function but during the R&D phase I can imagine it would come in quite > handy. > Thank you - that's what I meant. It would make dropping functions consistent with dropping other objects. Whether users then use this in production or only in development is up to them.
On Tue, Feb 04, 2014 at 10:56:28AM -0800, David Johnston wrote: > If you are doing version controlled upgrades you should not be using this > function but during the R&D phase I can imagine it would come in quite > handy. Or add Tom's remarks to a little corner of contrib/, or as Tom suggested, the docs, though I don't have a clear way to state it. One could actually add a reference to Tom's note to the comments section of the docs so that some mroe thinking could go into how to putting words about this in the docs. I agree that the function signature is part of the function. This is strange to people because when you're developing it's normal to think of functionname(args) as the thing you're changing, but in a system that allows overloading like Postgres that's not really true for production. A -- Andrew Sullivan ajs@crankycanuck.ca