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 <elein@varlena.com> -----

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"


============================================================
elein@varlena.com        Varlena, LLC        www.varlena.com

          PostgreSQL Consulting, Support & Training   

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.

----- End forwarded message -----

Thu, 5 Feb 2004 12:46:21 -0800
elein <elein@varlena.com>
[Resend: Domains and function]
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 <elein@varlena.com> -----
>
> 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

Fri, 20 Feb 2004 11:10:30 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: [HACKERS] [Resend: Domains and function]
Robert Treat <xzilla@users.sourceforge.net> writes:
> so ISTM that your example is certainly a deficiency if not a bug.

I believe it is a bug or at least an unimplemented feature in plpgsql:
plpgsql has its own implementation of casting, and is not aware that
casting to a domain should involve running check constraints.

The same might be true of the other PL languages, not sure.

			regards, tom lane

Fri, 20 Feb 2004 11:33:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] [Resend: Domains and function] 
The reason it is a bug rather than an unimplemented feature
is that it pokes a hole in the effectiveness of domains.
What good is a domain if it cannot be enforced everywhere
you use it?  That is like having a hole in referential
integrity.  (Though I admit not many people will run into
this until more people use domains.)

Shouldn't all function calls go (through fast path or fmgr
or the language manager?) to a centralized parameter marshalling?  
They should.  The informix reimplementation of postgres was
very firm about this.  It ensured parameter coersion consistency
across all languages. 

Centralizing the parameter marshalling will make it so
much easier to implement things like domains and 
arrays in various languages consistently and across
the board. Once the coersion it properly done in SQL,
then each language can decide the natural form for
the standard data types or punt to text.

elein

On Fri, Feb 20, 2004 at 11:33:39AM -0500, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > so ISTM that your example is certainly a deficiency if not a bug.
> 
> I believe it is a bug or at least an unimplemented feature in plpgsql:
> plpgsql has its own implementation of casting, and is not aware that
> casting to a domain should involve running check constraints.
> 
> The same might be true of the other PL languages, not sure.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Fri, 20 Feb 2004 09:55:27 -0800
elein <elein@varlena.com>
Re: [HACKERS] [Resend: Domains and function]
elein <elein@varlena.com> writes:
> Shouldn't all function calls go (through fast path or fmgr
> or the language manager?) to a centralized parameter marshalling?  

We're talking about what happens inside the function, not how you pass
parameters to it.

			regards, tom lane

Fri, 20 Feb 2004 13:03:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] [Resend: Domains and function] 
Right. Sorry. My brain was over on parameter issues and
I did not reread my original bug...


On Fri, Feb 20, 2004 at 01:03:08PM -0500, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > Shouldn't all function calls go (through fast path or fmgr
> > or the language manager?) to a centralized parameter marshalling?  
> 
> We're talking about what happens inside the function, not how you pass
> parameters to it.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Fri, 20 Feb 2004 11:02:23 -0800
elein <elein@varlena.com>
Re: [HACKERS] [Resend: Domains and function]