Обсуждение: [Resend: Domains and function]
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 -----
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
explicitcast
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
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
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
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)
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)