"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