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