Обсуждение: DECODE

Hi,

How does one do the equivalence of Oracle's DECODE in PostgreSQL?

select
  decode (v.media, 'V', 'VHS', 'L', 'Laser Disk', 'Unknown')
from
  videos v
;

(ie. if (v.media == 'V') return 'VHS';
     elsif (v.media == 'L') return 'Laser Disk';
     else  return 'Unknown'; )

thanks
dlink

David,

> How does one do the equivalence of Oracle's DECODE in PostgreSQL?
 >
> select
> decode (v.media, 'V', 'VHS', 'L', 'Laser Disk', 'Unknown')
 > from
 > videos v
 > ;

 Use a CASE statement:
 SELECT (CASE WHEN v.media = 'V' THEN 'VHS'
 WHEN v.media = 'L' THEN 'Laser Disk'
   ELSE 'Unknown' END) AS media_exp
 FROM videos v;

If you wanted, you could write a PL/pgSQL function to simplify this,
 using two array parameters to hold the lists.

 HOWEVER, it would be far better than both of the above, relationally,
to
 create a reference table populated with the appropriate values
 (media_code, media_name) and JOIN the reference table. This would
 prevent you from having to populate a special function on each and
every
 query!

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

David,

> You don't think having a separate reference table for each code
> lookup
> -- that is, making the datamodel more fully normalized will not
> impact
> performance?  Probably nominally because there will be so few rows in
> them.

It will impact performance no more than, and perhaps less than,
evaluating a multi-stage CASE statement for each row in the query.  And
this kind of performance concern is only an issue if you're trying to
run a public web site on budget hardware ... otherwise, the other
elements of your system will be more of a bottleneck than the query
parser!  I regularly use queries and views that involve 6-9 tables,
three UNIONS, and two sub-selects in each UNION ... and still get a 2-3
second response time on the 500mhz Celeron production machine.

I actually use a single table to store all my miscellaneous reference
codes in most databases.  Like:

CREATE TABLE misc_codes (
    code_type VARCHAR(20) NOT NULL,
    code_value VARCHAR(30) NOT NULL,
    code_desc VARCHAR(200) NOT NULL,
    CONSTRAINT codes_PK PRIMARY KEY (code_type, code_value)
);

This is an immensely convenient approach from a maintainence
perspective, although it has some drawbacks.  For one, one has to be
careful to filter the codes by code_type *before* any aggregate
operators are applied, or duplicate codes will result in bad aggregate
values.  If you have a completely star-topology database, it's probably
better to go the 100% normal way, and have a seperate table for each
code.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Josh Berkus wrote:
>
> David,
>
> > You don't think having a separate reference table for each code
> > lookup
> > -- that is, making the datamodel more fully normalized will not
> > impact
> > performance?  Probably nominally because there will be so few rows in
> > them.
>
> It will impact performance no more than, and perhaps less than,
> evaluating a multi-stage CASE statement for each row in the query.  And
> this kind of performance concern is only an issue if you're trying to
> run a public web site on budget hardware ... otherwise, the other
> elements of your system will be more of a bottleneck than the query
> parser!  I regularly use queries and views that involve 6-9 tables,
> three UNIONS, and two sub-selects in each UNION ... and still get a 2-3
> second response time on the 500mhz Celeron production machine.

Thanks.  Good advice.
Question: How big are those tables you're joining and uniting?  (disk
size, and number of rows).

David,

> >  I regularly use queries and views that involve 6-9 tables,
> > three UNIONS, and two sub-selects in each UNION ... and still get a
> 2-3
> > second response time on the 500mhz Celeron production machine.
>
> Thanks.  Good advice.
> Question: How big are those tables you're joining and uniting?  (disk
> size, and number of rows).

I'm not sure about disk size.  Some of the tables are reference lists,
with just a few dozen rows.  4 of the tables have at least 1000 rows
each.  Nothing large, or I'd use a faster processor.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco