The following bug has been logged on the website:
Bug reference: 7535
Logged by: Louis-David Mitterrand
Email address: l...@apartia.fr
PostgreSQL version: 9.2.0
Operating system: debian testing
Description:
/*error in 9.2*/
drop database error92;
create database error92;
\c error92
CREATE TABLE price (
id_price serial primary key
);
CREATE TABLE cabin_class (
id_cabin_class serial primary key,
cabin_class_name text not null
);
CREATE TABLE cabin_type (
id_cabin_type serial primary key,
id_cabin_class integer references cabin_class not null,
cabin_type_name text not null,
cabin_type_code text not null
);
CREATE TABLE cabin_category (
id_cabin_category serial primary key,
id_cabin_type integer references cabin_type not null,
cabin_cat_name text,
cabin_cat_code text
);
CREATE TABLE alert_cruise (
id_alert_cruise serial primary key,
/* id_cruise integer references cruise not null,
id_currency integer references currency,*/
enabled boolean default true not null,
md5_code text DEFAULT md5(now()::text || random()::text)
);
CREATE TABLE alert_cabin_category (
id_alert_cruise integer references alert_cruise,
id_cabin_category integer references cabin_category not null
);
CREATE TABLE alert_cabin_type (
id_alert_cruise integer references alert_cruise,
id_cabin_type integer references cabin_type not null
);
CREATE TABLE alert_cabin_class (
id_alert_cruise integer references alert_cruise,
id_cabin_class integer references cabin_class not null
);
CREATE VIEW alert_cruise_all AS
select c.*, ac.enabled
from (
-- ac.id_cabin_class is the first one, so other UNION parts will
-- use that column name as well
select 'class' as type,cl.cabin_class_name as type_name,ac.*
from alert_cabin_class ac
join cabin_class cl using (id_cabin_class)
union all
select 'type' as type,cl.cabin_class_name||case when
cl.cabin_class_name =
ct.cabin_type_name then '' else ' ~ '||ct.cabin_type_name end as
type_name,ac.*
from alert_cabin_type ac
join cabin_type ct using (id_cabin_type)
join cabin_class cl using (id_cabin_class)
union all
select 'category' as type,cl.cabin_class_name||
case when cl.cabin_class_name = ct.cabin_type_name
then '' else ' ~ '||ct.cabin_type_name end||' ~
'||cc.cabin_cat_code
as type_name,ac.*
from alert_cabin_category ac
join cabin_category cc
join cabin_type ct using (id_cabin_type)
join cabin_class cl using (id_cabin_class)
using (id_cabin_category)
)
as c join alert_cruise ac using (id_alert_cruise);
create view alert_to_category as
select ac.*, cl.cabin_class_name, ct.cabin_type_name, cc.cabin_cat_name,
cc.id_cabin_category, cc.cabin_cat_code,
case when type='class' then cl.cabin_class_name
when type='type' then ct.cabin_type_name
when type='category' then ct.cabin_type_name||' '||cc.cabin_cat_code
end as cabin_name
from alert_cruise_all ac
left join cabin_class cl on (
(ac.type = 'class' and cl.id_cabin_class=ac.id_cabin_class)
or (ac.type = 'type' and cl.id_cabin_class=(select id_cabin_class
from cabin_type where id_cabin_type=ac.id_cabin_class))
or (ac.type = 'category' and cl.id_cabin_class=(select
ct2.id_cabin_class from cabin_type ct2 join cabin_category cc2 using
(id_cabin_type) where cc2.id_cabin_category=ac.id_cabin_class))
)
join cabin_type ct on (
(ac.type = 'class' and ct.id_cabin_class=cl.id_cabin_class)
or (ac.type = 'type' and ct.id_cabin_type=ac.id_cabin_class)
or (ac.type = 'category' and ct.id_cabin_type=(select id_cabin_type
from cabin_category where id_cabin_category=ac.id_cabin_class))
)
join cabin_category cc on (
(ac.type = 'category' and
cc.id_cabin_category=ac.id_cabin_class)
or (ac.type != 'category' and ct.id_cabin_type=cc.id_cabin_type)
);
select
first_value(max(p.id_price)) over () as id_price1
,ac.cabin_name
from alert_to_category ac
join price p on (p.id_price=ac.id_alert_cruise)
group by ac.cabin_name;
/*
select
first_value(max(p.id_price)) over () as id_price1
,ac.cabin_name
from alert_to_category ac
join price p on (p.id_cruise=ac.id_cruise)
group by ac.cabin_name;
*/
/*EOF*/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
