Re: column label
От | Christoph Haller |
---|---|
Тема | Re: column label |
Дата | |
Msg-id | 3E81791F.8D120A60@rodos.fzk.de обсуждение исходный текст |
Ответ на | column label ("Ryan" <pgsql-sql@seahat.com>) |
Список | pgsql-sql |
> >> Ok I have a query in a program that looks something like this > >> > >> select > >> reference_number as foo, > >> '(10,10)'::point <-> point(x,y) as distance > >> from imagemap > >> where > >> index_number =3D '2322' and > >> block_number =3D '140' > >> order by distance > >> limit 1; > >> > >> I wanted to add a clause in where 'and distance < 30' > >> but according to the postgres manual it says I cannot do that for > >> WHERE or HAVING clauses, instead use the real name. > >> > >> However dropping the 'as distance' from my query gives me a column > >> name of '?column?' which happens to not be worth a dang thing. > >> > >> How can it be done? > >> > >> (I suddenly have a feeling its going to require a subselect) > > Your feeling is 50% accurate. If you want to use subselect, throw "<10" > > into this subselect, because you will have performance loss. > > > > You can also use whole function in where clause and order by: > > select > > reference_number as foo, > > '(10,10)'::point <-> point(x,y) as distance > > from imagemap > > where > > index_number =3D '2322' and > > block_number =3D '140' > > where '(10,10)'::point <-> point(x,y)<10 > > order by '(10,10)'::point <-> point(x,y) > > limit 1; > > > Thats kind of a pain... I'd rather perform the calculation once and then > do my order and where on the one value. Is there any reason why postgres > does not recognize labels under WHERE? > So how about select foo, distance from (select reference_number as foo, '(10,10)'::point <-> point(x,y) as distancefrom imagemap where index_number = '2322' and block_number = '140' ) as bar where distance < 30 order by distance limit 1; The reason why postgres does not recognize labels under WHERE is the WHERE clause is evaluated before column aliasing takes place. But there is this valuable feature of using a sub-SELECT as from_item, so this should not be a problem at all. Alternatively you could use selectreference_number,'(10,10)'::point <-> point(x,y) from imagemap alias (foo, distance) where index_number = '2322' and block_number = '140' and distance < 30 order by distancelimit 1; Does this help? Regards, Christoph
В списке pgsql-sql по дате отправления: