Skip site navigation (1) Skip section navigation (2)

Referencing a superselect's column by it's declared alias from a subselect fails

From: Casey Allen Shobe <cshobe(at)secureworks(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Referencing a superselect's column by it's declared alias from a subselect fails
Date: 2002-09-15 12:17:18
Message-ID: 200209150817.18406.cshobe@secureworks.net (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

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" = ui."info_type_id"
					where uit."info_type" = 'Position ID'
					and uit."info_type_name" = 'Default'
					and ui."user_id" = 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" = ui."info_type_id"
					where uit.info_type = 'Creator ID'
					and uit.info_type_name = 'Default'
					and ui."user_id" = ps."usesysid"
				),
				0
			) as "Creator ID"
	from only	"pg_shadow" as "ps"
	order by	"UID";

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

Lost Terminal.

Responses

pgsql-bugs by date

Next:From: Rod TaylorDate: 2002-09-15 14:11:04
Subject: Re: Postgres storing time in strange manner
Previous:From: Casey Allen ShobeDate: 2002-09-15 07:57:42
Subject: Postgres storing time in strange manner

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group