Adding variables to a query

query.sql

SELECT AVG(CAST(end_time AS time)) AS avg_end_time FROM "places" INNER JOIN "events" ON "events"."place_id" = "places"."id" AND events.category_id = 8 WHERE "places"."geocode_id" = 6096070 AND "places"."category_id" = 2 AND (place_events_count > 2) AND ((EXTRACT(EPOCH FROM end_time) - EXTRACT(EPOCH FROM start_time)) BETWEEN (4*3600) AND (12*3600) AND EXTRACT(HOUR FROM end_time) BETWEEN 11 AND 19) AND EXTRACT(DOW FROM end_time) IN (6,0);

  avg_end_time   
-----------------
 15:55:32.654876
(1 row)

I've got a set of queries like this, each with magic numbers like geocode_id = 6096070 (4*3600).
Right now I'm just editing the values in the query in psql rerunning, and copy/pasting.

I'd like to be able to add a list of geocodes (e.g. SF is 6096070, NYC is 123123), and get out this:

 geocode_name |  avg_end_time   
--------------+-----------------
 SF           | 15:55:32.654876
 NYC          | 12:34:56.78
(2 rows)

But I'd like to put the geocode_name to geocode (and also other magic numbers) table in the query.

Postgres' Common Table Expressions can help you define a table, that you can use to join against with your regular tables. (Beware! untested code :)

WITH geocode_id_names AS (
    SELECT 6096070 as id, 'SF' as name
    UNION ALL
    SELECT 123123, NYC
    UNION ALL
    ...),
SELECT geocode_id_names.name, AVG(...)
FROM places
    INNER JOIN events ...
    INNER JOIN geocode_id_names
        ON places.geocode_id = geocode_id_names.id ...

Alternatively, use values (based on hint from RhodiumToad IRC)

WITH q(id, name) AS (
    (VALUES(1, 'D'),
            (2, 'E'),
            (3, 'F'),
            (4, 'G') ))
SELECT * FROM q;