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

On Aug 9, 2012, at 7:32, Stefan Keller <sfkeller(at)gmail(dot)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 FROM source. 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general