'create or replace function' no longer allows parameters

Started by David Gardnerover 15 years ago4 messages
#1David Gardner
dgardner@creatureshop.com

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

#2Robert Haas
robertmhaas@gmail.com
In reply to: David Gardner (#1)
Re: 'create or replace function' no longer allows parameters

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

#3Alvaro Herrera
alvherre@commandprompt.com
In reply to: David Gardner (#1)
Re: 'create or replace function' no longer allows parameters

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

#4Josh Berkus
josh@agliodbs.com
In reply to: Alvaro Herrera (#3)
Re: 'create or replace function' no longer allows parameters

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