Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist) - Mailing list pgsql-general

Hi

I have two (hopefully) equivalent - and unfortunately very slow - queries which
"Select all buildings that have >1 pharmacies and >1 schools within 1000m".

In the first query there is an expression alias "b" and in the second
there are two expression aliases: "b" and "p".

Can someone tell me, why expression alias "p" is *not* recognized in
the WHERE clause - whereas alias "b" is (parantheses missing)?

And, has anyone an idea on how to reformulate this second query?

Stefan


SELECT way AS building_geometry
FROM
  (SELECT osm_id, way
   FROM osm_polygon
   WHERE tags @> hstore('building','yes')
  ) AS b
WHERE
 (SELECT count(*) > 1 FROM osm_poi AS p
  WHERE p.tags @> hstore('amenity','pharmacy')
  AND ST_DWithin(b.way,p.way,1000)
 )
 AND
 (SELECT count(*) > 1 FROM osm_poi AS p
  WHERE p.tags @> hstore('amenity','school')
  AND ST_DWithin(b.way,p.way,1000)
 )


SELECT b.way AS building_geometry
FROM
 (SELECT way FROM osm_polygon
  WHERE tags @> hstore('building','yes')
 ) AS b,
 (SELECT way, tags->'amenity' as value   FROM osm_poi
  WHERE (tags ? 'amenity')
 ) AS p
WHERE
 (SELECT count(*) > 1 FROM p
  WHERE p.value = 'pharmacy'
  AND ST_DWithin(b.way,p.way,1000)
 )
 AND
 (SELECT count(*) > 1 FROM p
  WHERE p.value = 'school'
  AND ST_DWithin(b.way,p.way,1000)
 )

ERROR:  relation "p" does not exist
LINE 10:  (SELECT count(*) > 1 FROM p
                                    ^

********** Error **********

ERROR: relation "p" does not exist
SQL state: 42P01
Character: 245