From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Matt Miller <pgsql(at)mattmillersf(dot)fastmail(dot)fm>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Convert Oracle DECODE to CASE Automatically |
Date: | 2006-11-29 23:03:43 |
Message-ID: | 20061129230343.GD30011@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Nov 29, 2006 at 04:55:49PM -0500, Tom Lane wrote:
> "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?
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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2006-11-29 23:06:57 | Re: Version 8.1 |
Previous Message | Jeff Davis | 2006-11-29 22:58:48 | Re: Version 8.1 |