Обсуждение: Referencing a superselect's column by it's declared alias from a subselect fails

In the following statement (which works), if I s/ps."usesysid"/"UID"/ in th=
e=20
two innermost subselects (such that it refers to the declared alias rather=
=20
than the true column name), it fails.  In DB2, it works in similar=20
situations.

create view    "users" as
    select        ps."usesysid" as "UID",
            ps."usename" as "Username",
            ps."passwd" as "Password",
            coalesce (
                (
                    select        cast (ui."info_value" as integer)
                    from only    "user_info" as "ui"
                    inner join only    "user_info_types" as "uit"
                    on        uit."type_id" =3D ui."info_type_id"
                    where uit."info_type" =3D 'Position ID'
                    and uit."info_type_name" =3D 'Default'
                    and ui."user_id" =3D ps."usesysid"
                ),
                0
            ) as "Position ID",
            coalesce (
                (
                    select        cast (ui."info_value" as integer)
                    from only    "user_info" as "ui"
                    inner join only    "user_info_types" as "uit"
                    on        uit."type_id" =3D ui."info_type_id"
                    where uit.info_type =3D 'Creator ID'
                    and uit.info_type_name =3D 'Default'
                    and ui."user_id" =3D ps."usesysid"
                ),
                0
            ) as "Creator ID"
    from only    "pg_shadow" as "ps"
    order by    "UID";

--=20
Casey Allen Shobe / Network Security Analyst & PHP Developer
SecureWorks, Inc. / 404.327.6339 x169 / Fax: 404.728.0144
cshobe@secureworks.net / http://www.secureworks.net
Content is my own and does not necessarily represent my company.

Lost Terminal.

Casey Allen Shobe <cshobe@secureworks.net> writes:
> In the following statement (which works), if I s/ps."usesysid"/"UID"/ in the
> two innermost subselects (such that it refers to the declared alias rather
> than the true column name), it fails.  In DB2, it works in similar
> situations.

I find that hard to believe.  If true, DB2 is broken.  An output-column
alias should not be visible in expressions for other output columns.
There are obvious circularity problems with allowing such a thing.

            regards, tom lane