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 <ch...@chriscurvey.com> To: salah jubeh <s_ju...@yahoo.com> Cc: pgsql <pgsql-general@postgresql.org> Sent: Fri, April 15, 2011 5:28:39 PM Subject: Re: [GENERAL] correlated query as a column and where clause On Fri, Apr 15, 2011 at 11:22 AM, salah jubeh <s_ju...@yahoo.com> 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!
