Re: please help with converting a view in oracle into postgresql readably code - Mailing list pgsql-sql

"Matthew Geddert" <geddert@yahoo.com> writes:
> create or replace view events_orders_states 
> as
> select  o.*,
> o_states.order_state
> from events_orders o,
>  (select
>  order_id,
>  decode (floor(avg (decode (reg_state, 
>                    'canceled', 0,
>                    'waiting', 1,
>                    'pending', 2,
>                    'shipped', 3,
>                    0))),
>              0, 'canceled',
>              1, 'incomplete',
>              2, 'incomplete',
>              3, 'fulfilled',
>              'void') as order_state
>  from events_registrations
>  group by order_id) o_states
> where o_states.order_id = o.order_id;

DECODE() is an Oracle-ism.  You can use the SQL-standard CASE construct
instead.  See

http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-conditional.html

Other than the DECODE() calls this should run fine in PG.
        regards, tom lane