constant column value in view with union

Поиск
Список
Период
Сортировка
От Sarah Officer
Тема constant column value in view with union
Дата
Msg-id 387BA3A7.6F937A4D@aries.tucson.saic.com
обсуждение исходный текст
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Alfred Perlstein
Дата:
Сообщение: Re: [GENERAL] Memory leak in FreeBSD?
Следующее
От: Sarah Officer
Дата:
Сообщение: Re: [GENERAL] constant column value in view with union