Thread: Visibility of table aliases in CTEs

Hello all,

In a WITH query, are table aliases within a CTE isolated from the rest
of the query? For example, if I do this -

  with my_cte as (
    select * from my_table t
  )
  ....

- can I re-use the alias "t" elsewhere in the same query?

I had a dig through the appropriate section of the docs, but the answer
wasn't obvious to me there.

Thanks,

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Raymond O'Donnell wrote
> Hello all,
>
> In a WITH query, are table aliases within a CTE isolated from the rest
> of the query? For example, if I do this -
>
>   with my_cte as (
>     select * from my_table t
>   )
>   ....
>
> - can I re-use the alias "t" elsewhere in the same query?
>
> I had a dig through the appropriate section of the docs, but the answer
> wasn't obvious to me there.

This seems like one of those "just try it and see what happens" kind of
question.  But since I'm mobile and cannot run SQL I'll just say that such
an alias is indeed hidden.  No different than:

Select * from (tbla as alpha natrual join tblb as beta) as ab natural join
tbla as alpha

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Visibility-of-table-aliases-in-CTEs-tp5778687p5778692.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 16/11/2013 15:48, David Johnston wrote:
> Raymond O'Donnell wrote
>> Hello all,
>>
>> In a WITH query, are table aliases within a CTE isolated from the rest
>> of the query? For example, if I do this -
>>
>>   with my_cte as (
>>     select * from my_table t
>>   )
>>   ....
>>
>> - can I re-use the alias "t" elsewhere in the same query?
>>
>> I had a dig through the appropriate section of the docs, but the answer
>> wasn't obvious to me there.
>
> This seems like one of those "just try it and see what happens" kind of
> question.  But since I'm mobile and cannot run SQL I'll just say that such
> an alias is indeed hidden.  No different than:
>
> Select * from (tbla as alpha natrual join tblb as beta) as ab natural join
> tbla as alpha


Thanks David - I did indeed try it, and it seemed to be the case, but as
I'm not any kind of expert I wanted to make sure that my empirical
results were correct. :-)

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie