Re: Can function results be used in WHERE?
| От | Aaron Bono |
|---|---|
| Тема | Re: Can function results be used in WHERE? |
| Дата | |
| Msg-id | bf05e51c0607102021i73ae93bey15f31a8ef91d007f@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Can function results be used in WHERE? (Bryce Nesbitt <bryce1@obviously.com>) |
| Список | pgsql-sql |
On 7/10/06, Bryce Nesbitt <bryce1@obviously.com> wrote:
You can also try
SELECT
SELECT
Aaron Bono wrote:
>
>
> On 7/10/06, *Bryce Nesbitt* <bryce1@obviously.com
> <mailto:bryce1@obviously.com>> wrote:
>
>
> I think it is ugly also, but no other syntax seems to work:
>
> stage=# select
> pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) as
> dist from eg_pod where dist < 1 order by dist desc limit 10;
> ERROR: column "dist" does not exist
>
>
> SELECT
> pod_code,
> lat,
> lon,
> calculate_distance(lat,lon,37.789629,-122.422082) as dist
> FROM eg_pod
> WHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1
> ORDER BY calculate_distance(lat,lon,37.789629,-122.422082) desc limit 10;
Yep, that works. I guess with IMMUTABLE it's even effecient.
But I have to pass 6 arguments, not 2. Is there a way to make it look
cleaner?
I had expected using the column label (e.g. "dist") to work with WHERE,
just as it does with ORDER BY.
You can also try
SELECT
pod_code,
lat,
lon,
dist
FROM (lat,
lon,
dist
SELECT
pod_code,
lat,
lon,
calculate_distance(lat,lon,37.789629,-122.422082) as dist
lat,
lon,
calculate_distance(lat,lon,37.789629,-122.422082) as dist
FROM eg_pod
) eg_prod
WHERE dist < 1
ORDER BY dist desc limit 10;
If the 37.789629 and -122.422082 are static values you can create a view for it. Otherwise you can create a function or stored procedure that takes 2 arguments and returns the results of the subquery.
Just some options. Not sure which you would prefer. I am sure there are more ways to do it.
-Aaron Bono
) eg_prod
WHERE dist < 1
ORDER BY dist desc limit 10;
If the 37.789629 and -122.422082 are static values you can create a view for it. Otherwise you can create a function or stored procedure that takes 2 arguments and returns the results of the subquery.
Just some options. Not sure which you would prefer. I am sure there are more ways to do it.
-Aaron Bono
В списке pgsql-sql по дате отправления: