Re: Expression alias not recognized in WHERE clause (ERROR: relation "p" does not exist)

On Aug 9, 2012, at 7:32, Stefan Keller <sfkeller@gmail.com> wrote:

> 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)?

The subqueries can make use of values attached to the outer (aliased) relation but you cannot use the alias itself as a
FROMsource.  The error is stemming from your use of "FROM p" inside the WHERE. 

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

Use a common table expression (CTE) (sql command: WITH).  Those can be attached to any FROM clause in the 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general