Thread: 7.0beta bug (or feature)?
The problem is in the very last function definition where date_week is defined with a date argument. It simply calls the date_week function that has a text argument, but apparently, the parser does not recognize the fact that a cast is present and tries to look for an existing function date_week(date) instead of date_week(text).
It looks like either there is a problem in the parser with casting, or there is a new way of doing things I should adapt to. Anyone know which it is?
Here is the error message:
ERROR: No such function 'date_week' with the specified attributes
Here is the SQL to recreate the problem:
-- Define PLPSQL Language ********************************
drop function plpgsql_call_handler ();
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';
-- This creates the plpgsql language
drop PROCEDURAL LANGUAGE 'plpgsql';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
-- Define PLTCL Language *********************************
drop function pltcl_call_handler ();
CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS
'/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C';
-- This creates the plpgsql language
drop PROCEDURAL LANGUAGE 'pltcl';
CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl'
HANDLER pltcl_call_handler
LANCOMPILER 'PL/TCL';
-- Convert date in 1999-01-15 format to the month of the year (1999,35)
-- calling sequence: date_week(ISO_date)
drop function date_week(text);
create function date_week(text) returns text as '
set spl [split $1 {-/. }]
set year [lindex $spl 0]
set month [string trimleft [lindex $spl 1] 0]
set day [lindex $spl 2]
if {$month > 0 && $month <= 31} {
set secs [clock scan "$month/$day/$year"]
} else {
set secs [clock scan "$month $day, $year"]
}
set week [clock format $secs -format "%U"]
if {$week == 0} {
return "[expr $year - 1]-52"
}
return "$year-$week"
' LANGUAGE 'pltcl';
drop function date_week(date);
create function date_week(date) returns text as '
select date_week($1::text);
' LANGUAGE 'sql';
Attachment
Kyle Bateman <kyle@actarg.com> writes: > This function would load OK in 6.5 but doesn't work in 7.0beta1: > create function date_week(date) returns text as ' > select date_week($1::text); > ' LANGUAGE 'sql'; > ERROR: No such function 'date_week' with the specified attributes > ... apparently, the parser does not recognize > the fact that a cast is present and tries to look for an existing > function date_week(date) instead of date_week(text). Yup, 7.0beta1 is missing a couple of lines of code needed to handle casts applied to function parameters. Thanks for catching that. I have patched current CVS sources; tonight's snapshot should have the fix. > It looks like either there is a problem in the parser with casting, or > there is a new way of doing things I should adapt to. 7.0 currently is a little snippier about casts than prior releases were; it wants the cast to equate directly to an available conversion. So what I'm getting from your example now is regression=# create function date_week(date) returns text as ' regression'# select date_week($1::text); regression'# ' LANGUAGE 'sql'; ERROR: Cannot cast type 'date' to 'text' because there isn't a text(date) function. But there is a text(timestamp) function, and a timestamp(date) function, so this works: regression=# create function date_week(date) returns text as ' regression'# select date_week($1::timestamp::text); regression'# ' LANGUAGE 'sql'; CREATE 6.5 would do the intermediate conversion to timestamp (then called datetime) silently, but 7.0 won't. Note that both versions will happily take select date_week(text($1)); and interpolate the intermediate conversion step as part of resolving the overloaded function name text(). 7.0 is only picky about conversions written as casts. I am not sure whether this should be regarded as a bug or a feature. On the one hand you could argue that ambiguous casts are a bad thing, but on the other hand, if text(foo) works, why shouldn't foo::text work? One thing to realize while considering whether to change this is that if we generalize the behavior of casts, we may also affect the behavior of implicit casts, such as the one applied to convert supplied data in an INSERT or UPDATE to the target column type. This could result in loss of error detection capability. Currently, both 6.5 and 7.0 do this: regression=# create table foo(f1 text); CREATE regression=# insert into foo values('now'::date); ERROR: Attribute 'f1' is of type 'text' but expression is of type 'date' You will need to rewrite or cast the expression but if we allow datevalue::text to work, then (barring still more pushups in the code) the above will be accepted. Should it be? Comments anyone? regards, tom lane
Tom Lane wrote: > > I am not sure whether this should be regarded as a bug or a feature. > On the one hand you could argue that ambiguous casts are a bad thing, > but on the other hand, if text(foo) works, why shouldn't foo::text work? > > One thing to realize while considering whether to change this is that if > we generalize the behavior of casts, we may also affect the behavior of > implicit casts, such as the one applied to convert supplied data in an > INSERT or UPDATE to the target column type. This could result in loss > of error detection capability. Currently, both 6.5 and 7.0 do this: > > regression=# create table foo(f1 text); > CREATE > regression=# insert into foo values('now'::date); > ERROR: Attribute 'f1' is of type 'text' but expression is of type 'date' > You will need to rewrite or cast the expression > > but if we allow datevalue::text to work, then (barring still more > pushups in the code) the above will be accepted. Should it be? > > Comments anyone? > What if you could to a "set AutoCasting=yes" just as you might set the datestyle variable. Then the DBA could decide whether type mismatches should be quitely translated or reported?