Re: Drop all overloads of a function without knowing parameter types - Mailing list pgsql-general

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.