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:
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