Re: [GENERAL] constant column value in view with union

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
>
> ************