Thread: 'create or replace function' no longer allows parameters
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
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
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
> 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