Thread: 'create or replace function' no longer allows parameters

'create or replace function' no longer allows parameters

From
David Gardner
Date:
In 8.4.4 I used to be able to rename input parameters via "create or 
replace function".
In 9.0 beta2  this no longer is allowed, and I get a descriptive message 
informing me to use
drop function instead, but I couldn't find this documented anywhere as a 
change between 8.4 and 9.0.
--------------
test=# CREATE FUNCTION test_plpgsql(IN a integer) RETURNS integer AS
test-# $BODY$
test$# BEGIN
test$#     RETURN $1;
test$# END;
test$# $BODY$
test-# LANGUAGE 'plpgsql' STABLE;
CREATE FUNCTION
test=# SELECT * FROM  test_plpgsql(34); test_plpgsql
--------------           34
(1 row)

test=# CREATE OR REPLACE FUNCTION test_plpgsql(IN b integer) RETURNS 
integer AS
$BODY$
BEGIN    RETURN $1;
END;
$BODY$
LANGUAGE 'plpgsql' STABLE;
CREATE FUNCTION
test=# SELECT * FROM  test_plpgsql(34); test_plpgsql
--------------           34
(1 row)

test=# \df+ test_plpgsql                                                             List of 
functions Schema |     Name     | Result data type | Argument data types |  
Type  | Volatility |  Owner   | Language |  Source code   | Description

--------+--------------+------------------+---------------------+--------+------------+----------+----------+----------------+-------------
public| test_plpgsql | integer          | b integer           | 
 
normal | stable     | dgardner | plpgsql  |                |
                                               : BEGIN
                                         :     RETURN $1;
                                            : END;
                                     :
 
(1 row)
-----------------------

psql (9.0beta2)
Type "help" for help.

test=# CREATE FUNCTION test_plpgsql(IN a integer) RETURNS integer AS
test-# $BODY$
test$# BEGIN
test$#     RETURN $1;
test$# END;
test$# $BODY$
test-# LANGUAGE 'plpgsql' STABLE;
CREATE FUNCTION
test=# CREATE OR REPLACE FUNCTION test_plpgsql(IN b integer) RETURNS 
integer AS
test-# $BODY$
test$# BEGIN
test$#     RETURN $1;
test$# END;
test$# $BODY$
test-# LANGUAGE 'plpgsql' STABLE;
ERROR:  cannot change name of input parameter "a"
HINT:  Use DROP FUNCTION first.

-- 
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgardner@creatureshop.com




Re: 'create or replace function' no longer allows parameters

From
Robert Haas
Date:
On Wed, Jun 9, 2010 at 8:55 PM, David Gardner <dgardner@creatureshop.com> wrote:
> In 8.4.4 I used to be able to rename input parameters via "create or replace
> function".
> In 9.0 beta2  this no longer is allowed, and I get a descriptive message
> informing me to use
> drop function instead, but I couldn't find this documented anywhere as a
> change between 8.4 and 9.0.

This is a consequence of the change to allow functions to be called
using named notation rather than positional notation.

http://developer.postgresql.org/pgdocs/postgres/sql-syntax-calling-funcs.html

There could be a view somewhere that depends on calling the function
using the old input parameter name, and we have no way to detect that
case, so we disallow changing or dropping names (but you can add them
where they aren't already present) so as to avoid silent view
breakage.  It's a bit unfortunate, but I'm not sure there's much help
for it.

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


Re: 'create or replace function' no longer allows parameters

From
Alvaro Herrera
Date:
Excerpts from David Gardner's message of mié jun 09 20:55:36 -0400 2010:
> In 8.4.4 I used to be able to rename input parameters via "create or 
> replace function".
> In 9.0 beta2  this no longer is allowed, and I get a descriptive message 
> informing me to use
> drop function instead, but I couldn't find this documented anywhere as a 
> change between 8.4 and 9.0.

It's this patch:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=e7eb1113f8a95e9927fdbe9cc6fb0ac101612be2#patch7

It should probably be mentioned in the incompatibilities section of the
9.0 release notes.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: 'create or replace function' no longer allows parameters

From
Josh Berkus
Date:
> It's this patch:
> http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=e7eb1113f8a95e9927fdbe9cc6fb0ac101612be2#patch7
>
> It should probably be mentioned in the incompatibilities section of the
> 9.0 release notes.

Addition will be included in my release notes patch, coming today.

--                                   -- Josh Berkus                                     PostgreSQL Experts Inc.
                           http://www.pgexperts.com