Обсуждение: newbie - syntax question

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;
=============================================================

On Thu, 9 May 2002, McCaffity, Ray (Contractor) wrote:

> 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 "=" .

> select distinct
>        uid              = 'c' + trim(char(m.ctc_uid)),
>        command          = e.msg_command,

I think you probably want something like
e.msg_command AS command,

...
> > select distinct
> >        uid              = 'c' + trim(char(m.ctc_uid)),
> >        command          = e.msg_command,
> I think you probably want something like
> e.msg_command AS command,

and something like

  ('c' + trim(char(m.ctc_uid))) as uid,

which have forms more closely tied to SQL9x standards than your original
form.

While you are at it you might want to evaluate your needs for character
strings and choose the type (char, varchar, or text) which best matches
your application. You may be able to eliminate some of these
conversions.

                    - Thomas