Обсуждение: constant column value in view with union
I am converting some Oracle stuff to Postgres (or attempting to convert it :) ), and I'm having trouble with my views. I have figured out the syntax difference between Oracle & Postgres, I think, but Postgres doesn't seem to know the type of a constant text column. How can I make this work? The view is something like this: create view Foo as select a, b, c, 'OK' as status from table1 where ... union select a, b, c, 'BAD' as status from table1 where ... My Oracle view has 4 unions with fairly complicated where clauses. What I want to do in the end is select status from Foo where b = 'baz'; In Oracle, the system figures out that status is a text column. In Postgres I am warned: NOTICE: Attribute 'alert_status' has an unknown type Relation created; continue Whey I type > \d Foo Postgres responds ERROR: typeidTypeRelid: Invalid type - oid = 0 Any suggestions would be appreciated. Thanks, Sarah Officer officers@aries.tucson.saic.com
Oh dear. The problem I mentioned here occured when I just based my view on one select statement. When I add the union clause, I am informed that views can't handle unions. Is there a standard work-around? I'm afraid I was planning a couple of other views which look at this one. Thanks, Sarah Officer officers@aries.tucson.saic.com Sarah Officer wrote: > > I am converting some Oracle stuff to Postgres (or attempting to > convert it :) ), and I'm having trouble with my views. I have > figured out the syntax difference between Oracle & Postgres, I > think, but Postgres doesn't seem to know the type of a constant text > column. How can I make this work? > > The view is something like this: > > create view Foo as > select a, b, c, 'OK' as status > from table1 > where ... > union > select a, b, c, 'BAD' as status > from table1 > where ... > > My Oracle view has 4 unions with fairly complicated where clauses. > What I want to do in the end is > > select status from Foo where b = 'baz'; > > In Oracle, the system figures out that status is a text column. In > Postgres I am warned: > > NOTICE: Attribute 'alert_status' has an unknown type > Relation created; continue > Whey I type > > > \d Foo > > Postgres responds > > ERROR: typeidTypeRelid: Invalid type - oid = 0 > > Any suggestions would be appreciated. > > Thanks, > > Sarah Officer > officers@aries.tucson.saic.com > > ************
Sarah Officer wrote: > Oh dear. The problem I mentioned here occured when I just based my > view on one select statement. When I add the union clause, I am > informed that views can't handle unions. Is there a standard > work-around? I'm afraid I was planning a couple of other views > which look at this one. No, views are a problem at the moment. One of the biggest problems is that the plan to execute a view is stored in a database table and there is a limit of 8192 bytes per tuple. This is quite quickly exceeded by the size of the plan. Jan Wieck has implemented a compressed text type, which will allow the system to store larger views in the view table and is working on a mechanism to store fields that still don't fit in a secondary table. The compressed data type will be in the next version of postgres (Feb-Mar 2000), whether the secondary tables make it is a bit touch and go. Views with unions is on the todo list, but i don't know whether that is going to happen by the next version. Adriaan
Sarah Officer wrote: > > I am converting some Oracle stuff to Postgres (or attempting to > convert it :) ), and I'm having trouble with my views. I have > figured out the syntax difference between Oracle & Postgres, I > think, but Postgres doesn't seem to know the type of a constant text > column. How can I make this work? > > The view is something like this: > > create view Foo as > select a, b, c, 'OK' as status > from table1 > where ... > union > select a, b, c, 'BAD' as status > from table1 > where ... > select a, b, c, 'OK'::varchar(3) as status ... Concerning the union: 1. create separate views with the same structure 2. use a select of the form: select a, b, c, status from view1 where ... union select a, b, c, status from view2 where ... Regards Wim
Sarah Officer wrote: > > The view is something like this: > > > > create view Foo as > > select a, b, c, 'OK' as status > > from table1 > > where ... > > union > > select a, b, c, 'BAD' as status > > from table1 > > where ... > > you could try it with a CASE WHEN ... THEN ... ELSE ... END oliver