Re: correlated query as a column and where clause

[salah jubeh <s_jubeh@xxxxxxxxx>]

Hello All,

The following query give me what I want. 

Select nspname, COALESCE(t_count.count,0) as num_of_tables, COALESCE(v_count.count,0) as num_of_views     
FROM pg_namespace   
Left Join (SELECT schemaname, count(*) as count FROM pg_tables group by schemaname) t_count on (t_count.schemaname = nspname)
Left Join (SELECT schemaname, count(*) as count FROM pg_views  group by schemaname) v_count on (v_count.schemaname = nspname)
where nspname !~* 'pg_' and (COALESCE(t_count.count,0)+COALESCE(v_count.count,0) <= 2)
order by 1,2;

But, why I can not  use the alias of the select statement ( as in the original post)  in the where clause.    

Regards

 

---

From: Chris Curvey <chris@xxxxxxxxxxxxxxx>
To: salah jubeh <s_jubeh@xxxxxxxxx>
Cc: pgsql <pgsql-general@xxxxxxxxxxxxxx>
Sent: Fri, April 15, 2011 5:28:39 PM
Subject: Re: correlated query as a column and where clause

On Fri, Apr 15, 2011 at 11:22 AM, salah jubeh <s_jubeh@xxxxxxxxx> wrote:

Hello All,

I am wondering,  why I can not add the following  '  A > 10'  in the where  clause  i.e.   'where nspname !~* 'pg_'  and A > 10'

Select nspname, (SELECT count(*) as count FROM pg_tables where schemaname = nspname) as A
FROM pg_namespace
where nspname !~* 'pg_'

I can't answer your question directly, but I would rewrite the query as:

select pg_namespace.nspname, count(*)

from pg_namespace

join pg_tables on pg_namespace.nspname = pg_tables.schemaname

where pg_namespace.nspname not like 'pg_%'

group by pg_namespace.nspname

having count(*) > 10

Thanks in advance

--
Ignoring that little voice in my head since 1966!