> -----Original Message----- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of air > Sent: Thursday, October 04, 2012 3:32 PM > To: pgsql-sql@postgresql.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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
