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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 20 August 2014 15:25, Lori Corbani <Lori.Corbani@jax.org> wrote:

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).’

Further on in that same paragraph (although I looked at the 9.3 docs) it says:
'To do that, you must drop and recreate the function.'

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Yes, I am doing a DROP and then a CREATE OR REPLACE

Let me read over your example...

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, August 20, 2014 9:40 AM
To: Lori Corbani; pgsql-general@postgresql.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.klaver@aklaver.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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I *am* definitely dropping the function first. I still get the same error.

-----Original Message-----
From: Alban Hertroys [mailto:haramrae@gmail.com]
Sent: Wednesday, August 20, 2014 9:44 AM
To: Lori Corbani
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

On 20 August 2014 15:25, Lori Corbani <Lori.Corbani@jax.org> wrote:

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).’

Further on in that same paragraph (although I looked at the 9.3 docs) it says:
'To do that, you must drop and recreate the function.'

--
If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/20/2014 06:51 AM, Lori Corbani wrote:

I *am* definitely dropping the function first. I still get the same error.

Well we need to see the actual sequence to figure this out. Best guess,
is you have more than one function with that name.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.klaver@aklaver.com]
Sent: Wednesday, August 20, 2014 9:40 AM
To: Lori Corbani; pgsql-general@postgresql.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.klaver@aklaver.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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/20/2014 07:00 AM, Lori Corbani wrote:

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

Can we see the actual sequence in psql with the command outputs?

Also what is escaping the $?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/20/2014 07:00 AM, Lori Corbani wrote:

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

It worked here on Postgres 9.3 and un-escaping the $.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Include the types in the drop

Sent from my iPhone

On Aug 20, 2014, at 7:59 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 08/20/2014 06:51 AM, Lori Corbani wrote:

I *am* definitely dropping the function first. I still get the same error.

Well we need to see the actual sequence to figure this out. Best guess, is you have more than one function with that name.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Lori Corbani <Lori.Corbani@jax.org> writes:

My example:

DROP FUNCTION ACC_setMax();

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

This is not the right thing: you need to include the parameters in the
drop command. ACC_setMax() is a totally different function from
ACC_setMax(int, varchar).

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

OK...if I manually run this within psql it is fine.

But I have a shell script in which I am wrapping a call to 'psql'...and I guess it does not like how things are being passed in when I do it this way.

So, yes, it is working correctly when I copy/paste my script directly into psql.

Will try a different approach to my wrapper.

Many thanks!
Lori

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, August 20, 2014 10:11 AM
To: Lori Corbani; pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

On 08/20/2014 07:00 AM, Lori Corbani wrote:

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

Can we see the actual sequence in psql with the command outputs?

Also what is escaping the $?

--
Adrian Klaver
adrian.klaver@aklaver.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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This worked! Many, many, thanks!

-----Original Message-----
From: Rob Sargent [mailto:robjsargent@gmail.com]
Sent: Wednesday, August 20, 2014 10:15 AM
To: Adrian Klaver
Cc: Lori Corbani; Alban Hertroys; pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

Include the types in the drop

Sent from my iPhone

On Aug 20, 2014, at 7:59 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 08/20/2014 06:51 AM, Lori Corbani wrote:

I *am* definitely dropping the function first. I still get the same error.

Well we need to see the actual sequence to figure this out. Best guess, is you have more than one function with that name.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/20/2014 08:42 AM, Lori Corbani wrote:

OK...if I manually run this within psql it is fine.

But I have a shell script in which I am wrapping a call to 'psql'...and I guess it does not like how things are being passed in when I do it this way.

So, yes, it is working correctly when I copy/paste my script directly into psql.

Will try a different approach to my wrapper.

An additional thing to watch out for is the mixed case in the function
name. To demonstrate using a table name:

aklaver@test=> create table "CamelCap_Quoted" (id int);
CREATE TABLE
aklaver@test=> create table CamelCap_Not_Quoted (id int);
CREATE TABLE
aklaver@test=> select * from CamelCap_Quoted;
ERROR: relation "camelcap_quoted" does not exist
LINE 1: select * from CamelCap_Quoted;
^
aklaver@test=> select * from "CamelCap_Quoted";
id

----

(0 rows)

aklaver@test=> select * from CamelCap_Not_Quoted;

id

----

(0 rows)

aklaver@test=> \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+----------
public | CamelCap_Quoted | table | aklaver
public | camelcap_not_quoted | table | aklaver

If the name is quoted the case is preserved and you have to quote the
name to get the same object. A lot of frameworks/ORMS automatically
quote object names so this something to watch out for. In the unquoted
case the name is folded to lower case by default. So you can get in a
situation where you have both the quoted and unquoted name and not be
working on the object you think you are.

Many thanks!
Lori

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

It worked when I included the parameter list in the DROP statement.

Thank you!
Lori

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, August 20, 2014 11:54 AM
To: Lori Corbani; pgsql-general@postgresql.org
Subject: Re: [GENERAL] create function : change name of input parameter

On 08/20/2014 08:42 AM, Lori Corbani wrote:

OK...if I manually run this within psql it is fine.

But I have a shell script in which I am wrapping a call to 'psql'...and I guess it does not like how things are being passed in when I do it this way.

So, yes, it is working correctly when I copy/paste my script directly into psql.

Will try a different approach to my wrapper.

An additional thing to watch out for is the mixed case in the function name. To demonstrate using a table name:

aklaver@test=> create table "CamelCap_Quoted" (id int); CREATE TABLE aklaver@test=> create table CamelCap_Not_Quoted (id int); CREATE TABLE aklaver@test=> select * from CamelCap_Quoted;
ERROR: relation "camelcap_quoted" does not exist LINE 1: select * from CamelCap_Quoted;
^
aklaver@test=> select * from "CamelCap_Quoted";
id

----

(0 rows)

aklaver@test=> select * from CamelCap_Not_Quoted;

id

----

(0 rows)

aklaver@test=> \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+----------
public | CamelCap_Quoted | table | aklaver
public | camelcap_not_quoted | table | aklaver

If the name is quoted the case is preserved and you have to quote the name to get the same object. A lot of frameworks/ORMS automatically quote object names so this something to watch out for. In the unquoted case the name is folded to lower case by default. So you can get in a situation where you have both the quoted and unquoted name and not be working on the object you think you are.

Many thanks!
Lori

--
Adrian Klaver
adrian.klaver@aklaver.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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general