Thread: Convert Oracle DECODE to CASE Automatically

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.  Now I'm not
finding that thread.  Is there such a beast?

"Matt Miller" <pgsql@mattmillersf.fastmail.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

Tom Lane wrote:
> -- repeat up to the most number of decode items you need to support

Does PG's function overloading make varargs difficult?

--
Guy Rouillier

On Wed, Nov 29, 2006 at 04:55:49PM -0500, Tom Lane wrote:
> "Matt Miller" <pgsql@mattmillersf.fastmail.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?

Yes.  I don't know whether it's pertinent, but it would be really nice
to have functions that can take SETOF as input, too.

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!