Thread: Overloading

On Fri, Jun 13, 2008 at 06:11:43PM -0700, Ralph Smith wrote:

> I get:
> ERROR:  cannot change return type of existing function
> HINT:  Use DROP FUNCTION first.

Don't use CREATE OR REPLACE for the second one.  The OR REPLACE is
trying to replace a function of the same name.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Ralph Smith wrote:

I never did get an answer to this.

I get:

ERROR:  cannot change return type of existing function

HINT:  Use DROP FUNCTION first.

********** Error **********

ERROR: cannot change return type of existing function

SQL state: 42P13

Hint: Use DROP FUNCTION first.

When I try to:

  CREATE OR REPLACE FUNCTION check_date_ymd(given_date varchar) RETURNS BOOLEAN  AS

and

  CREATE OR REPLACE FUNCTION check_date_ymd(given_date varchar, OUT isvalid boolean, OUT ryear int, OUT rmonth int, OUT rday int)  AS

I just spent a few hours on this today.  I wanted my function to return info in several columns.
When you specify OUT parameters like this, it's telling the function to return column names of "isvalid, ryear, rmonth, & rday" with the corresponding data types. If you DROP the existing function " DROP FUNCTION check_date_ymd(given_date varchar)" then you can run CREATE OR REPLACE FUNCTION. The following snippet is from the help file.

An alternative way of describing a function's results is to define it with output parameters, as in this example:

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT $1 + $2'
LANGUAGE SQL;

SELECT add_em(3,7);add_em
--------    10
(1 row)

This is not essentially different from the version of add_em shown in Section 34.4.1, “SQL Functions on Base Types”. The real value of output parameters is that they provide a convenient way of defining functions that return several columns. For example,

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;
SELECT * FROM sum_n_product(11,42);sum | product
-----+--------- 53 |     462
(1 row)

What has essentially happened here is that we have created an anonymous composite type for the result of the function. The above example has the same end result as

CREATE TYPE sum_prod AS (sum int, product int);

CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

Using OUT params creates a different output type.

Hope this helps
Dennis