Skip site navigation (1) Skip section navigation (2)

Re: Convert Oracle DECODE to CASE Automatically

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Matt Miller" <pgsql(at)mattmillersf(dot)fastmail(dot)fm>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Convert Oracle DECODE to CASE Automatically
Date: 2006-11-29 21:55:49
Message-ID: 4954.1164837349@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Matt Miller" <pgsql(at)mattmillersf(dot)fastmail(dot)fm> writes:
> I thought I saw a thread (here or on -hackers, or somewhere) where
> someone created a C program or something to automatically convert
> Oracle's DECODE expression into an ANSI CASE expression.

You could just use a compatibility function:

create function decode(anyelement,anyelement,anyelement,anyelement)
  returns anyelement as $$
    select case $1 when $2 then $3 else $4 end
  $$ language sql immutable;

create function decode(anyelement,anyelement,anyelement,anyelement,anyelement,anyelement)
  returns anyelement as $$
    select case $1 when $2 then $3 when $4 then $5 else $6 end
  $$ language sql immutable;

-- repeat up to the most number of decode items you need to support

It's annoying that we only have one "anyelement" pseudotype; this
formulation constrains the input and result types to be the same,
when logically they could be different.  Perhaps this example is
a sufficient argument for inventing "anyelement2".  Then it'd look like

create function decode(anyelement,anyelement,anyelement2,anyelement,anyelement2,anyelement2)
  returns anyelement2 as ...

I remember we talked about multiple placeholder types back when we
designed the polymorphic-function feature, but we didn't put it in
for lack of a compelling use-case.  Is this one?

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Guy RouillierDate: 2006-11-29 22:11:31
Subject: Re: Convert Oracle DECODE to CASE Automatically
Previous:From: Guy RouillierDate: 2006-11-29 21:43:57
Subject: Re: Only MONO/WinForms is a way to go

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group