Re: create function : change name of input parameter

My example:

DROP FUNCTION ACC_setMax();

CREATE OR REPLACE FUNCTION ACC_setMax (
increment int,
prefixPart varchar(30) = 'MGI:'
)
RETURNS VOID AS
\$\$

BEGIN

/* Increment the max MGI Accession Number by @increment */
update ACC_AccessionMax
set maxNumericPart = maxNumericPart + increment
where prefixPart = prefixPart
;

END;
\$\$
LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION ACC_setMax(int,varchar) TO public;

*****

When I change the parameter "prefixPart" to "v_prefixPart"....I get the error.

prefixPart varchar(30) = 'MGI:'

where prefixPart = prefixPart

changed to

v_ prefixPart varchar(30) = 'MGI:'

where prefixPart = v_prefixPart

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Wednesday, August 20, 2014 9:40 AM
To: Lori Corbani; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] create function : change name of input parameter

On 08/20/2014 06:25 AM, Lori Corbani wrote:
> Using Postgres Version 9.0.4:
>
> We are migrating our Sybase stored procedures to Postgres and need to
> be able to drop/replace the SPs, making needed changes to input
> parameters to fix issues/differences between Sybase and Postgres.
>
> However, we keep getting this error when we drop/replace the PG/SP:

Are you actually doing DROP and then CREATE OR REPLACE FUNCTION or just doing CREATE OR REPLACE FUNCTION ?

The first case will work, the second will not:

test=> CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ test$> BEGIN
test$> tax := subtotal * 0.06;
test$> END;
test$> $$ LANGUAGE plpgsql;
CREATE FUNCTION

test=> CREATE OR REPLACE FUNCTION public.sales_tax(stotal real, OUT tax
real)
RETURNS real
LANGUAGE plpgsql
AS $function$
BEGIN

tax := subtotal * 0.06;

END;

$function$

ERROR: cannot change name of input parameter "subtotal"
HINT: Use DROP FUNCTION sales_tax(real) first.

test=> drop function sales_tax(real) ;
DROP FUNCTION

test=> CREATE OR REPLACE FUNCTION public.sales_tax(stotal real, OUT tax
real)
RETURNS real
LANGUAGE plpgsql
AS $function$
BEGIN
tax := subtotal * 0.06;
END;
$function$
;
CREATE FUNCTION

>
> 'ERROR : cannot change name of input parameter'
>
> The 'create function' documentation states:
>
> 'You cannot change the name already assigned to any input parameter
> (although you can add names to parameters that had none before).'
>
> Other than a complete restore of the database from a dump that does
> **not** contain the original SP...is there any other way to override this
> constraint?
>
> This makes debugging **extremely** difficult. If you don't have your
> input parameters completely correct the first time...you're hosed.
>
> Many thanks.
>
> Lori
>
> The information in this email, including attachments, may be
> confidential and is intended solely for the addressee(s). If you believe
> you received this email by mistake, please notify the sender by return
> email as soon as possible.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s). If you believe you received this email by mistake, please notify the sender by return email as soon as possible.