newbie - syntax question

Поиск
Список
Период
Сортировка
От McCaffity, Ray (Contractor)
Тема newbie - syntax question
Дата
Msg-id 9669B05099E9D411B6E400B0D0AA476C5C37EA@epg.lewis.army.mil
обсуждение исходный текст
Ответы Re: newbie - syntax question  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-general
We are in the process of switching from Ingres II to Postgres,
we use a lot of views, most of them seem to straight across without too
many problems.  But some are a little more diffcult. Does anyone know
the syntax, on how to do view aliasing in postgres?  Here is an example
view script from ingres.  I've looked all through the PDF manuals, and I can
create generic views, but these don't seem to work.  This one seems to fail
with all of the "=" .

Ray
===========================================
drop view vw_qwed_all_pos_v1;

create view vw_qwed_all_pos_v1
as

select distinct
       uid              = 'c' + trim(char(m.ctc_uid)),
       command          = e.msg_command,
       source           = e.msg_source,
       track_num        = e.track_number,
       class_name       = e.class_name,
       type             = Ifnull(ulk.description,'UNKNOWN'),
       type_alt         = e.type,
       pennant          = e.pennant_number,
       force            = ifnull(flk.description,'UNKNOWN'),
       track_type       = char(e.track_type),
       time             = p.conv_pos_dtg,
       mgrs             = p.mgrs,
       lat              = p.latitude,
       lon              = p.longitude,
       deleted          = d.deleted,
       easting          = p.raw_easting,
       northing         = p.raw_northing,
       flag             = e.flag
from
      (((((qwed_master_status_v1 as m
      left join qwed_entity_index_v1 as e on e.ctc_uid = m.ctc_uid)
      left join
          qwed_pos_v1 p on e.ctc_uid = p.ctc_uid and
          p.raw_northing != '' and p.raw_easting != '')
      left join
          qwed_uid_status_v1 as d on e.ctc_uid = d.uid
                               and d.uid_type = 'CTC')
      left join
          qwed_lk_force_type_v1 flk on e.force_code = flk.force_code)
      left join
          qwed_lk_unit_type_v1 ulk on e.type = ulk.code)


union

select distinct
       uid              = 'c' + trim(char(m.ctc_uid)),
       command          = e.msg_command,
       source           = e.msg_source,
       track_num        = e.track_number,
       class_name       = e.class_name,
       type             = Ifnull(ulk.description,'UNKNOWN'),
       type_alt         = e.type,
       pennant          = e.pennant_number,
       force            = ifnull(flk.description,'UNKNOWN'),
       track_type       = char(e.track_type),
       time             = p.conv_xpos_dtg,
       mgrs             = p.mgrs,
       lat              = p.latitude,
       lon              = p.longitude,
       deleted          = d.deleted,
       easting          = p.raw_easting,
       northing         = p.raw_northing,
       flag             = e.flag
from
      (((((qwed_master_xpos_v1 as m
      left join qwed_entity_index_v1 e on e.ctc_uid = m.ctc_uid)
      left join
          qwed_xpos_v1 p on e.ctc_uid = p.ctc_uid and
          p.raw_northing != '' and p.raw_easting != '')
      left join
          qwed_uid_status_v1 as d on e.ctc_uid = d.uid
                               and d.uid_type = 'CTC')
      left join
          qwed_lk_force_type_v1 flk on e.force_code = flk.force_code)
      left join
          qwed_lk_unit_type_v1 ulk on e.type = ulk.code)

union

select distinct
       uid              = 'j' + trim(char(m.junit_uid)),
       command          = e.msg_command,
       source           = e.msg_source,
       track_num        = e.track_number,
       class_name       = e.name,
       type             = Ifnull(ulk.description,'UNKNOWN'),
       type_alt         = e.type,
       pennant          = '-',
       force            = ifnull(flk.description,'UNKNOWN'),
       track_type       = char(e.track_type),
       time             = p.conv_jpos_dtg,
       mgrs             = p.mgrs,
       lat              = p.latitude,
       lon              = p.longitude,
       deleted          = d.deleted,
       easting          = p.raw_easting,
       northing         = p.raw_northing,
       flag             = e.flag
from
      (((((qwed_master_jpos_v1 as m
      left join qwed_junit_index_v1 e on e.junit_uid = m.junit_uid)
      left join
          qwed_jpos_v1 p on e.junit_uid = p.junit_uid and
          p.raw_northing != '' and p.raw_easting != '')
      left join
          qwed_uid_status_v1 as d on e.junit_uid = d.uid
                                 and d.uid_type = 'JUNIT')
      left join
          qwed_lk_force_type_v1 flk on e.force = flk.force_code)
      left join
          qwed_lk_unit_type_v1 ulk on e.type = ulk.code);

 grant select on vw_qwed_all_pos_v1 to public;
=============================================================



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

Предыдущее
От: "Nigel J. Andrews"
Дата:
Сообщение: Re: trouble with (lack of) indexing
Следующее
От: Tom Lane
Дата:
Сообщение: Re: trouble with (lack of) indexing