Thread: 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