Re: Weird results when using schemas

On Mon, 21 Nov 2005, Geert Jansen wrote:

> I'm experiencing some weird results with SELECT queries when I recently
> switched to using schemas. Basically, the same query works OK when I set
> my search_path to include the schema, but it doesn't when I qualify the
> tables in my query.
>
> One query that demonstrates the behaviour is (apologies for the long query).
>
> shs=> SELECT
> shs.city.id,shs.city.code,shs.city.name,shs.city.description,shs.city.mod_date,shs.city.mod_user
> FROM (shs.city AS city INNER JOIN shs.object_city_relationship ON
> shs.object_city_relationship.city_id = city.id INNER JOIN shs.object AS
> object ON shs.object_city_relationship.object_id = object.id) WHERE
> object.id = 1;
> NOTICE: adding missing FROM-clause entry for table "city"
> id | code | name | description |
> mod_date | mod_user
> ----+--------+--------+------------------------+----------------------------+----------
> 1 | alanya | Alanya | Alanya, the nice city. | 2005-11-21
> 00:00:40.502701 | 0
> 2 | bodrum | Bodrum | bodrum | 2005-11-21
> 00:03:53.786452 | 0
> (2 rows)
>
> Note the WARNING that "city" is missing in the FROM clause. I don't
> understand this as it seems to be there.

After using "shs.city as city", I don't believe it's correct to use
shs.city in the select list as I think that name is not exported from the
FROM clause due to the correlation name.