PATCH: Improve DROP FUNCTION hint

Started by Dean Rasheedover 13 years ago3 messages

dean.a.rasheed@gmail.com

over 13 years ago

1 attachment(s)

Hi,

Attached is a small patch to improve the HINT message produced by
CREATE OR REPLACE FUNCTION when the new function definition conflicts
with the old definition. With this patch the hint now includes the
function's name and signature as a directly pasteable SQL command. So,
for example, instead of

psql:functions.sql:70: ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION first.

it now says

psql:functions.sql:70: ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION foo(integer,integer) first.

which saves having to open the file, find the function and then type
in the DROP statement manually.

Regards,
Dean

Attachments:

drop-fn-hint.patchapplication/octet-stream; name=drop-fn-hint.patchDownload

diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
new file mode 100644
index ae71b93..da9470c
*** a/src/backend/catalog/pg_proc.c
--- b/src/backend/catalog/pg_proc.c
*************** ProcedureCreate(const char *procedureNam
*** 404,410 ****
  			ereport(ERROR,
  					(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  					 errmsg("cannot change return type of existing function"),
! 					 errhint("Use DROP FUNCTION first.")));
  
  		/*
  		 * If it returns RECORD, check for possible change of record type
--- 404,411 ----
  			ereport(ERROR,
  					(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  					 errmsg("cannot change return type of existing function"),
! 					 errhint("Use DROP FUNCTION %s first.",
!                              format_procedure(HeapTupleGetOid(oldtup)))));
  
  		/*
  		 * If it returns RECORD, check for possible change of record type
*************** ProcedureCreate(const char *procedureNam
*** 427,433 ****
  						(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  					errmsg("cannot change return type of existing function"),
  				errdetail("Row type defined by OUT parameters is different."),
! 						 errhint("Use DROP FUNCTION first.")));
  		}
  
  		/*
--- 428,435 ----
  						(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  					errmsg("cannot change return type of existing function"),
  				errdetail("Row type defined by OUT parameters is different."),
! 						 errhint("Use DROP FUNCTION %s first.",
!                                  format_procedure(HeapTupleGetOid(oldtup)))));
  		}
  
  		/*
*************** ProcedureCreate(const char *procedureNam
*** 469,475 ****
  							(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  					   errmsg("cannot change name of input parameter \"%s\"",
  							  old_arg_names[j]),
! 							 errhint("Use DROP FUNCTION first.")));
  			}
  		}
  
--- 471,478 ----
  							(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  					   errmsg("cannot change name of input parameter \"%s\"",
  							  old_arg_names[j]),
! 							 errhint("Use DROP FUNCTION %s first.",
!                                      format_procedure(HeapTupleGetOid(oldtup)))));
  			}
  		}
  
*************** ProcedureCreate(const char *procedureNam
*** 492,498 ****
  				ereport(ERROR,
  						(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  						 errmsg("cannot remove parameter defaults from existing function"),
! 						 errhint("Use DROP FUNCTION first.")));
  
  			proargdefaults = SysCacheGetAttr(PROCNAMEARGSNSP, oldtup,
  											 Anum_pg_proc_proargdefaults,
--- 495,502 ----
  				ereport(ERROR,
  						(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  						 errmsg("cannot remove parameter defaults from existing function"),
! 						 errhint("Use DROP FUNCTION %s first.",
!                                  format_procedure(HeapTupleGetOid(oldtup)))));
  
  			proargdefaults = SysCacheGetAttr(PROCNAMEARGSNSP, oldtup,
  											 Anum_pg_proc_proargdefaults,
*************** ProcedureCreate(const char *procedureNam
*** 518,524 ****
  					ereport(ERROR,
  							(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  							 errmsg("cannot change data type of existing parameter default value"),
! 							 errhint("Use DROP FUNCTION first.")));
  				newlc = lnext(newlc);
  			}
  		}
--- 522,529 ----
  					ereport(ERROR,
  							(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
  							 errmsg("cannot change data type of existing parameter default value"),
! 							 errhint("Use DROP FUNCTION %s first.",
!                                      format_procedure(HeapTupleGetOid(oldtup)))));
  				newlc = lnext(newlc);
  			}
  		}
diff --git a/src/test/regress/expected/polymorphism.out b/src/test/regress/expected/polymorphism.out
new file mode 100644
index 1e87953..a65e3b9
*** a/src/test/regress/expected/polymorphism.out
--- b/src/test/regress/expected/polymorphism.out
*************** select dfunc(10,20);
*** 1026,1032 ****
  create or replace function dfunc(a variadic int[]) returns int as
  $$ select array_upper($1, 1) $$ language sql;
  ERROR:  cannot remove parameter defaults from existing function
! HINT:  Use DROP FUNCTION first.
  \df dfunc
                                        List of functions
   Schema | Name  | Result data type |               Argument data types               |  Type  
--- 1026,1032 ----
  create or replace function dfunc(a variadic int[]) returns int as
  $$ select array_upper($1, 1) $$ language sql;
  ERROR:  cannot remove parameter defaults from existing function
! HINT:  Use DROP FUNCTION dfunc(integer[]) first.
  \df dfunc
                                        List of functions
   Schema | Name  | Result data type |               Argument data types               |  Type  
*************** returns record as $$
*** 1239,1251 ****
    select $1, $2;
  $$ language sql;
  ERROR:  cannot change name of input parameter "c"
! HINT:  Use DROP FUNCTION first.
  create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric)
  returns record as $$
    select $1, $2;
  $$ language sql;
  ERROR:  cannot change name of input parameter "c"
! HINT:  Use DROP FUNCTION first.
  drop function dfunc(varchar, numeric);
  --fail, named parameters are not unique
  create function testfoo(a int, a int) returns int as $$ select 1;$$ language sql;
--- 1239,1251 ----
    select $1, $2;
  $$ language sql;
  ERROR:  cannot change name of input parameter "c"
! HINT:  Use DROP FUNCTION dfunc(character varying,numeric) first.
  create or replace function dfunc(a varchar = 'def a', out _a varchar, numeric = NULL, out _c numeric)
  returns record as $$
    select $1, $2;
  $$ language sql;
  ERROR:  cannot change name of input parameter "c"
! HINT:  Use DROP FUNCTION dfunc(character varying,numeric) first.
  drop function dfunc(varchar, numeric);
  --fail, named parameters are not unique
  create function testfoo(a int, a int) returns int as $$ select 1;$$ language sql;
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
new file mode 100644
index 5f20c93..9fbdd83
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
*************** CREATE OR REPLACE FUNCTION foo(in f1 int
*** 439,445 ****
  RETURNS record
  AS 'select $1+1' LANGUAGE sql;
  ERROR:  cannot change return type of existing function
! HINT:  Use DROP FUNCTION first.
  CREATE OR REPLACE FUNCTION foor(in f1 int, out f2 int, out text)
  AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
  SELECT f1, foor(f1) FROM int4_tbl;
--- 439,445 ----
  RETURNS record
  AS 'select $1+1' LANGUAGE sql;
  ERROR:  cannot change return type of existing function
! HINT:  Use DROP FUNCTION foo(integer) first.
  CREATE OR REPLACE FUNCTION foor(in f1 int, out f2 int, out text)
  AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
  SELECT f1, foor(f1) FROM int4_tbl;
*************** SELECT * FROM dup('xyz'::text);
*** 521,527 ****
  CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
  AS 'select $1, array[$1,$1]' LANGUAGE sql;
  ERROR:  cannot change name of input parameter "f1"
! HINT:  Use DROP FUNCTION first.
  DROP FUNCTION dup(anyelement);
  -- equivalent behavior, though different name exposed for input arg
  CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
--- 521,527 ----
  CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
  AS 'select $1, array[$1,$1]' LANGUAGE sql;
  ERROR:  cannot change name of input parameter "f1"
! HINT:  Use DROP FUNCTION dup(anyelement) first.
  DROP FUNCTION dup(anyelement);
  -- equivalent behavior, though different name exposed for input arg
  CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)

Robert Haas

robertmhaas@gmail.com

over 13 years ago

In reply to: Dean Rasheed (#1)

Re: PATCH: Improve DROP FUNCTION hint

On Sat, Jun 9, 2012 at 11:42 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

Hi,

Attached is a small patch to improve the HINT message produced by
CREATE OR REPLACE FUNCTION when the new function definition conflicts
with the old definition. With this patch the hint now includes the
function's name and signature as a directly pasteable SQL command. So,
for example, instead of

psql:functions.sql:70: ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION first.

it now says

psql:functions.sql:70: ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION foo(integer,integer) first.

which saves having to open the file, find the function and then type
in the DROP statement manually.

+1.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas

robertmhaas@gmail.com

over 13 years ago

In reply to: Robert Haas (#2)

Re: PATCH: Improve DROP FUNCTION hint

On Mon, Jun 11, 2012 at 11:12 AM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sat, Jun 9, 2012 at 11:42 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:

Hi,

Attached is a small patch to improve the HINT message produced by
CREATE OR REPLACE FUNCTION when the new function definition conflicts
with the old definition. With this patch the hint now includes the
function's name and signature as a directly pasteable SQL command. So,
for example, instead of

psql:functions.sql:70: ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION first.

it now says

psql:functions.sql:70: ERROR: cannot change return type of existing function
HINT: Use DROP FUNCTION foo(integer,integer) first.

which saves having to open the file, find the function and then type
in the DROP statement manually.

+1.

Committed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company