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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
