Re: Overloading

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.

34.4.3. Functions with Output Parameters

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"
<xfunc-sql.html#xfunc-sql-base-functions>. 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