plpgsql should be trying to coerce the return value to the functions return
type:
rms=# create or replace function retval(integer) returns text as ' begin
return $1::integer; end; ' language 'plpgsql';
CREATE FUNCTION
rms=# select retval(1) || ' is text';
?column?
-----------
1 is text
(1 row)
rms=# select retval(1) + 1 ;
ERROR: Unable to identify an operator '+' for types 'text' and 'integer'
You will have to retype this query using an explicit cast
and it should error accordingly if it can not do so:
rms=# create or replace function retval2(text) returns integer as ' begin
return $1; end; ' language 'plpgsql';
CREATE FUNCTION
rms=# select retval2('one');
WARNING: Error occurred while executing PL/pgSQL function retval2
WARNING: while casting return value to function's return type
ERROR: pg_atoi: error in "one": can't parse "one"
so ISTM that your example is certainly a deficiency if not a bug.
hmm..examples above on 7.3, which didnt support check constraints, so this is
potentially different on 7.4.
Robert Treat
On Thursday 05 February 2004 15:46, elein wrote:
> I sent this a while ago to general and then
> hackers and got no response.
>
> The question is whether to qualify the return value
> of a function when it returns a domain with
> a check clause.
>
> I believe it should--otherwise the domain is
> only useful on insert and is not acting
> like a full fledged type. However, I suspect
> that there is no underlying support for
> type checks in the general system.
>
> Elein
>
> ----- Forwarded message from elein <[EMAIL PROTECTED]> -----
>
> I can create a function with a domain and
> define it to return a domain.
>
> The parameter is checked to see if it qualifies
> in the constraint of the domain, however, the
> return value is not.
>
> Is this a bug? Is the author of the function
> responsible for re-inforcing the constraint
> at runtime?
>
> This is the test case in 7.4:
>
> =# create domain one2hundred AS integer
> -# DEFAULT '1' CONSTRAINT email_domain check( VALUE > 0 AND VALUE <=100
> ); CREATE DOMAIN
> =#
> =# create function gb52_add( one2hundred )
> -# returns one2hundred as
> -# '
> '# BEGIN
> '# RETURN $1 + 10;
> '# END;
> '# ' language 'plpgsql';
> CREATE FUNCTION
> =#
> =# select gb52_add( 80);
> gb52_add
> ----------
> 90
> (1 row)
>
> =# select gb52_add( 100);
> gb52_add
> ----------
> 110
> (1 row)
>
> =# select gb52_add( 90);
> gb52_add
> ----------
> 100
> (1 row)
>
> =# select gb52_add( 91);
> gb52_add
> ----------
> 101
> (1 row)
>
> =# select gb52_add( 191);
> ERROR: value for domain one2hundred violates check constraint
> "email_domain"
>
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
