Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
Greg Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
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 signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
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. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
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( xy, 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. [EMAIL PROTECTED] On Sat, Sep 06, 2003 at 12:47:07PM -0400, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] 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(tenhundred,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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Needed function IF(expr, expr, expr)
Peter Eisentraut [EMAIL PROTECTED] 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(tenhundred,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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
