From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|

To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |

Cc: | Marek Lewczuk <newsy(at)lewczuk(dot)com>, pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |

Subject: | Re: Needed function IF(expr, expr, expr) |

Date: | 2003-09-06 16:47:07 |

Message-ID: | 29227.1062866827@sss.pgh.pa.us |

Views: | Raw Message | Whole Thread | Download mbox | Resend email |

Thread: | |

Lists: | pgsql-general pgsql-hackers |

Peter Eisentraut <peter_e(at)gmx(dot)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

- Re: Needed function IF(expr, expr, expr) at 2003-09-06 14:05:28 from Peter Eisentraut

- Re: Needed function IF(expr, expr, expr) at 2003-09-06 18:22:08 from elein
- Re: [GENERAL] Needed function IF(expr, expr, expr) at 2003-09-07 23:00:03 from Greg Stark
- Re: Needed function IF(expr, expr, expr) at 2003-09-07 23:25:30 from Rod Taylor

From | Date | Subject | |
---|---|---|---|

Next Message | Marek Lewczuk | 2003-09-06 17:09:08 | Re: Needed function IF(expr, expr, expr) |

Previous Message | Jeff Eckermann | 2003-09-06 16:32:24 | Re: Needed function IF(expr, expr, expr) |

From | Date | Subject | |
---|---|---|---|

Next Message | Tom Lane | 2003-09-06 16:52:13 | Re: [PATCHES] Warning for missing createlang |

Previous Message | Jeff Eckermann | 2003-09-06 16:32:24 | Re: Needed function IF(expr, expr, expr) |