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