Re: correlated query as a column and where clause

On Fri, Apr 15, 2011 at 11:22 AM, salah jubeh <s_jubeh(at)yahoo(dot)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!