Improve error reporting for DROP FUNCTION/PROCEDURE/AGGREGATE/ROUTINE.

These commands allow the argument type list to be omitted if there is
just one object that matches by name.  However, if that syntax was
used with DROP IF EXISTS and there was more than one match, you got
a "function ... does not exist, skipping" notice message rather than a
truthful complaint about the ambiguity.  This was basically due to
poor factorization and a rats-nest of logic, so refactor the relevant
lookup code to make it cleaner.

Note that this amounts to narrowing the scope of which sorts of
error conditions IF EXISTS will bypass.  Per discussion, we only
intend it to skip no-such-object cases, not multiple-possible-matches
cases.

Per bug #15572 from Ash Marath.  Although this definitely seems like
a bug, it's not clear that people would thank us for changing the
behavior in minor releases, so no back-patch.

David Rowley, reviewed by Julien Rouhaud and Pavel Stehule

Discussion: https://postgr.es/m/15572-ed1b9ed09503de8a@postgresql.org

2 files changed, 53 insertions, 0 deletions

diff --git a/src/test/regress/expected/drop_if_exists.out b/src/test/regress/expected/drop_if_exists.out
index b80c5ed2d87..6a174677177 100644
--- a/src/test/regress/expected/drop_if_exists.out
+++ b/src/test/regress/expected/drop_if_exists.out
@@ -301,3 +301,32 @@ DROP TYPE IF EXISTS no_such_schema.foo;
 NOTICE:  schema "no_such_schema" does not exist, skipping
 DROP VIEW IF EXISTS no_such_schema.foo;
 NOTICE:  schema "no_such_schema" does not exist, skipping
+-- Check we receive an ambiguous function error when there are
+-- multiple matching functions.
+CREATE FUNCTION test_ambiguous_funcname(int) returns int as $$ select $1; $$ language sql;
+CREATE FUNCTION test_ambiguous_funcname(text) returns text as $$ select $1; $$ language sql;
+DROP FUNCTION test_ambiguous_funcname;
+ERROR:  function name "test_ambiguous_funcname" is not unique
+HINT:  Specify the argument list to select the function unambiguously.
+DROP FUNCTION IF EXISTS test_ambiguous_funcname;
+ERROR:  function name "test_ambiguous_funcname" is not unique
+HINT:  Specify the argument list to select the function unambiguously.
+-- cleanup
+DROP FUNCTION test_ambiguous_funcname(int);
+DROP FUNCTION test_ambiguous_funcname(text);
+-- Likewise for procedures.
+CREATE PROCEDURE test_ambiguous_procname(int) as $$ begin end; $$ language plpgsql;
+CREATE PROCEDURE test_ambiguous_procname(text) as $$ begin end; $$ language plpgsql;
+DROP PROCEDURE test_ambiguous_procname;
+ERROR:  procedure name "test_ambiguous_procname" is not unique
+HINT:  Specify the argument list to select the procedure unambiguously.
+DROP PROCEDURE IF EXISTS test_ambiguous_procname;
+ERROR:  procedure name "test_ambiguous_procname" is not unique
+HINT:  Specify the argument list to select the procedure unambiguously.
+-- Check we get a similar error if we use ROUTINE instead of PROCEDURE.
+DROP ROUTINE IF EXISTS test_ambiguous_procname;
+ERROR:  routine name "test_ambiguous_procname" is not unique
+HINT:  Specify the argument list to select the routine unambiguously.
+-- cleanup
+DROP PROCEDURE test_ambiguous_procname(int);
+DROP PROCEDURE test_ambiguous_procname(text);
diff --git a/src/test/regress/sql/drop_if_exists.sql b/src/test/regress/sql/drop_if_exists.sql
index c1d30bc4a5f..8a791b1ef2d 100644
--- a/src/test/regress/sql/drop_if_exists.sql
+++ b/src/test/regress/sql/drop_if_exists.sql
@@ -271,3 +271,27 @@ DROP TEXT SEARCH TEMPLATE IF EXISTS no_such_schema.foo;
 DROP TRIGGER IF EXISTS foo ON no_such_schema.bar;
 DROP TYPE IF EXISTS no_such_schema.foo;
 DROP VIEW IF EXISTS no_such_schema.foo;
+
+-- Check we receive an ambiguous function error when there are
+-- multiple matching functions.
+CREATE FUNCTION test_ambiguous_funcname(int) returns int as $$ select $1; $$ language sql;
+CREATE FUNCTION test_ambiguous_funcname(text) returns text as $$ select $1; $$ language sql;
+DROP FUNCTION test_ambiguous_funcname;
+DROP FUNCTION IF EXISTS test_ambiguous_funcname;
+
+-- cleanup
+DROP FUNCTION test_ambiguous_funcname(int);
+DROP FUNCTION test_ambiguous_funcname(text);
+
+-- Likewise for procedures.
+CREATE PROCEDURE test_ambiguous_procname(int) as $$ begin end; $$ language plpgsql;
+CREATE PROCEDURE test_ambiguous_procname(text) as $$ begin end; $$ language plpgsql;
+DROP PROCEDURE test_ambiguous_procname;
+DROP PROCEDURE IF EXISTS test_ambiguous_procname;
+
+-- Check we get a similar error if we use ROUTINE instead of PROCEDURE.
+DROP ROUTINE IF EXISTS test_ambiguous_procname;
+
+-- cleanup
+DROP PROCEDURE test_ambiguous_procname(int);
+DROP PROCEDURE test_ambiguous_procname(text);