Re: Calling the CTE for multiple inputs

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)org] On Behalf Of air
> Sent: Thursday, October 04, 2012 3:32 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] Calling the CTE for multiple inputs
>
> I have a CTE that takes top left and bottom right latitude/longitude
values
> along with a start and end date and it then calculates the amount of user
> requests that came from those coordinates per hourly intervals between the
> given start and end date. However, I want to execute this query for about
> 2600 seperate 4-tuples of lat/lon corner values instead of typing them in
one-
> by-one. How would I do that? The code is as below:
>
> AND lat BETWEEN '40' AND '42'
> AND lon BETWEEN '28' AND '30'

I don't really follow but if I understand correctly you want to generate
2600 distinct rows containing values like (40, 42, 28, 30)?

You could use "generate_series()" to generate each individual number along
with a row_number and then join them all together:

SELECT lat_low, lat_high, long_low, long_high
FROM (SELECT ROW_NUMBER() OVER () AS index,
generate_series(...) AS lat_low) lat_low_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
lat_high) lat_high_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
long_low) long_low_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
long_high) long_high_rel

You may (probably will) need to move the generate_series into a FROM clause
in the sub-query but the concept holds.

Then in the main query you'd simply...

AND lat BETWEEN lat_low AND lat_high
AND lon BETWEEN long_low AND long_high

HTH

David J.