Skip site navigation (1) Skip section navigation (2)

BUG #7535: ERROR: variable not found in subplan target list

From: ldm(at)apartia(dot)fr
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7535: ERROR: variable not found in subplan target list
Date: 2012-09-12 14:25:30
Message-ID: E1TBnsg-0007rH-Cv@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      7535
Logged by:          Louis-David Mitterrand
Email address:      ldm(at)apartia(dot)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*/




pgsql-bugs by date

Next:From: Magnus HaganderDate: 2012-09-12 16:41:44
Subject: Re: BUG #7534: walreceiver takes long time to detect n/w breakdown
Previous:From: Alvaro HerreraDate: 2012-09-12 13:05:39
Subject: Re: BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group