Thread: Re: [GENERAL] Needed function IF(expr, expr, expr)
Peter Eisentraut <peter_e@gmx.net> writes:
> Marek Lewczuk writes:
>> Currently I have big problem with function IF(), below the description
>> of this function from MySQL manual.
> You cannot implement this kind of function, unless you want to create one
> version for each data type combination.
As of 7.4, one can avoid the data type problem with a polymorphic
function:
regression=# create function if (bool,anyelement,anyelement) returns anyelement
regression-# as 'select case when $1 then $2 else $3 end' language sql;
CREATE FUNCTION
However, there are some limitations:
regression=# select if(true, 33, 44);
if
----
33
(1 row)
regression=# select if(true, 33, 44.4);
ERROR: function if(boolean, integer, numeric) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit typecasts.
regression=# select if(true, 'a','b');
ERROR: could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN
You can hack around these problems by adding explicit casts:
regression=# select if(true, 'a'::text,'b');
if
----
a
(1 row)
but I wonder whether we shouldn't allow all-UNKNOWN inputs to be
resolved as TEXT in this situation, as we do when working directly with
CASE.
BTW, I started out this email intending to point out that a function
cannot replace CASE in general because the function will insist on
evaluating all its arguments, which is a behavior you do not want for
CASE, and I'd imagine not for MySQL's IF() either. (But I dunno, maybe
their IF() does evaluate the "unused" argument. Anyone know?)
However, as of 7.4, that problem is gone too. If you write the function
just as above (language sql, volatile, not strict) then the planner will
inline it and indeed what you get is a CASE. Watch this:
regression=# explain select * from tenk1 where if(ten<hundred,unique1,unique2) = 44;
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..508.00 rows=50 width=244)
Filter: (CASE WHEN (ten < hundred) THEN unique1 ELSE unique2 END = 44)
(2 rows)
So we do actually have a sort-of-credible way to make a user-defined
function that emulates IF(). I think we might be able to do Oracle's
DECODE() as well, though I don't know its exact definition. (You'd
still need to make several of 'em to handle differing numbers of
arguments, but that seems well within the bounds of feasibility.)
Any comments on the UNKNOWN issue? It's not too late to change that for
7.4, if we have consensus that we should.
regards, tom lane
If the function is defined with ANY* and you defer typing the arguments until the first reference then I think you will get what you want with the CASE statement. If the function is called if( x>y, x+1, y), the first reference is in the argument list and so should be typed there. But if you pass constants or non-expressions, then delaying the typing would enable a better fit when using ANY*. Letting the first usage define the type would leverage the existing expression handling work as is without special cases. It would also avoid implicit casts of unexpected argument types. If the operation in the function is dependent on the arguments being, say some kind of numeric or a class of types we ought to raise an error if they are incompatible. Of course this would ONLY be for ANY arguments. A function declared with a non-generic type needs to have arguments of the declared types. --elein@varlena.com On Sat, Sep 06, 2003 at 12:47:07PM -0400, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Marek Lewczuk writes: > >> Currently I have big problem with function IF(), below the description > >> of this function from MySQL manual. > > > You cannot implement this kind of function, unless you want to create one > > version for each data type combination. > > As of 7.4, one can avoid the data type problem with a polymorphic > function: > > regression=# create function if (bool,anyelement,anyelement) returns anyelement > regression-# as 'select case when $1 then $2 else $3 end' language sql; > CREATE FUNCTION > > However, there are some limitations: > > regression=# select if(true, 33, 44); > if > ---- > 33 > (1 row) > > regression=# select if(true, 33, 44.4); > ERROR: function if(boolean, integer, numeric) does not exist > HINT: No function matches the given name and argument types. You may need to add explicit typecasts. > > regression=# select if(true, 'a','b'); > ERROR: could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN > > You can hack around these problems by adding explicit casts: > > regression=# select if(true, 'a'::text,'b'); > if > ---- > a > (1 row) > > but I wonder whether we shouldn't allow all-UNKNOWN inputs to be > resolved as TEXT in this situation, as we do when working directly with > CASE. > > BTW, I started out this email intending to point out that a function > cannot replace CASE in general because the function will insist on > evaluating all its arguments, which is a behavior you do not want for > CASE, and I'd imagine not for MySQL's IF() either. (But I dunno, maybe > their IF() does evaluate the "unused" argument. Anyone know?) > > However, as of 7.4, that problem is gone too. If you write the function > just as above (language sql, volatile, not strict) then the planner will > inline it and indeed what you get is a CASE. Watch this: > > regression=# explain select * from tenk1 where if(ten<hundred,unique1,unique2) = 44; > QUERY PLAN > -------------------------------------------------------------------------- > Seq Scan on tenk1 (cost=0.00..508.00 rows=50 width=244) > Filter: (CASE WHEN (ten < hundred) THEN unique1 ELSE unique2 END = 44) > (2 rows) > > So we do actually have a sort-of-credible way to make a user-defined > function that emulates IF(). I think we might be able to do Oracle's > DECODE() as well, though I don't know its exact definition. (You'd > still need to make several of 'em to handle differing numbers of > arguments, but that seems well within the bounds of feasibility.) > > Any comments on the UNKNOWN issue? It's not too late to change that for > 7.4, if we have consensus that we should. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
> However, as of 7.4, that problem is gone too. If you write the function > just as above (language sql, volatile, not strict) then the planner will > inline it and indeed what you get is a CASE. Watch this: Hm. I wonder if there are cases of people using functions like this with user-defined volatile functions depending on the function's side effects happening the correct number of times. Or do volatile functions not get inlined like this? > So we do actually have a sort-of-credible way to make a user-defined > function that emulates IF(). I think we might be able to do Oracle's > DECODE() as well, though I don't know its exact definition. (You'd > still need to make several of 'em to handle differing numbers of > arguments, but that seems well within the bounds of feasibility.) I think there's a problem implementing decode() surrounding NULL: SELECT decode(col, 'foo', 1, NULL, 2, 3) would mean: SELECT CASE WHEN col='foo' THEN 1 WHEN col IS NULL THEN 2 ELSE 3 END To do it I think you would need a iseq() function that compared NULLs as being equal. -- greg
> Any comments on the UNKNOWN issue? It's not too late to change that for
> 7.4, if we have consensus that we should.
I would actually prefer to get UNKNOWN so I can apply my own default
type, but we're not even given the chance to resolve the unknown issue
ourselves.
CREATE OR REPLACE FUNCTION if(bool,anyelement,anyelement)
RETURNS anyelement
AS 'SELECT
CASE WHEN $2 is of (unknown) THEN
CASE WHEN $1 THEN $2::point ELSE $3::point END
ELSE
CASE WHEN $1 THEN $2 ELSE $3 END
END' language SQL;
CREATE FUNCTION
rbt=# select if(true, '33', '44');
ERROR: could not determine ANYARRAY/ANYELEMENT type because input is
UNKNOWN
Attachment
Rod Taylor <rbt@rbt.ca> writes:
>> Any comments on the UNKNOWN issue? It's not too late to change that for
>> 7.4, if we have consensus that we should.
> I would actually prefer to get UNKNOWN so I can apply my own default
> type, but we're not even given the chance to resolve the unknown issue
> ourselves.
> CREATE OR REPLACE FUNCTION if(bool,anyelement,anyelement)
> RETURNS anyelement
> AS 'SELECT
> CASE WHEN $2 is of (unknown) THEN
> CASE WHEN $1 THEN $2::point ELSE $3::point END
> ELSE
> CASE WHEN $1 THEN $2 ELSE $3 END
> END' language SQL;
There's no chance of that working --- the parser has to be able to
determine the result type of a function invocation without reference
to the function body. (Otherwise CREATE OR REPLACE FUNCTION invalidates
every use of the function.)
I don't feel that the anyelement in -> anyelement out mechanism is the
last word in polymorphism, though. Care to propose additional features
of the same kind? If you can find a way to describe the behavior you
want in terms of the function signature, it'd be worth considering ...
regards, tom lane
Greg Stark <gsstark@mit.edu> writes:
> Hm. I wonder if there are cases of people using functions like this with
> user-defined volatile functions depending on the function's side effects
> happening the correct number of times. Or do volatile functions not get
> inlined like this?
SQL functions can't have side effects, at least not if they are simple
SELECTs, which is the only kind that gets inlined.
> To do it I think you would need a iseq() function that compared NULLs as being
> equal.
No, just
CASE WHEN (col = checkval) OR (col IS NULL AND checkval IS NULL)...
regards, tom lane
> I don't feel that the anyelement in -> anyelement out mechanism is the
> last word in polymorphism, though. Care to propose additional features
> of the same kind? If you can find a way to describe the behavior you
> want in terms of the function signature, it'd be worth considering ...
For my immediate purposes the output is a known type. It is the input
that would be useful if it was passed through as unknown, or effectively
function as a placeholder if a stronger match cannot be found.
Due to inherited poorly typed data I find myself doing quite a bit of
"X_orNULL(anyelement) returns X". This takes quite a bit of interesting
structure to make it work on the current system.
CASE WHEN $1 IS OF (X) then $1
WHEN $1 IS OF (unknown) AND cancastX($1) THEN $1::X
ELSE NULL::X
END
Another useful function would be an extension of IS OF with output
somewhat like format_type. Returning a string of the datatype based upon
the value passed to it.
getType(anyelement) RETURNS text
Order of type match for unknown:
- Exact match first -- function(unknown) returns <type>
- Cast match second
- Anyelement match with defined return type should be supplied as
UNKNOWN (per function examples above)
If wanted, Anyelement match with anyelement return type could be
converted to text. Perhaps this is best described as a fallback cast
(when anyelement is unknown, autocast to X)
CREATE FUNCTION x(anyelement) RETURNS anyelement
....
LANGUAGE SQL RETURNS TYPE text ON UNKNOWN OUTPUT;
Without this clause an error would be thrown as unknown is not a valid
output.