Re: correlated query as a column and where clause

[Chris Curvey <chris@xxxxxxxxxxxxxxx>]

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!