From: | salah jubeh <s_jubeh(at)yahoo(dot)com> |
---|---|
To: | chris(at)chriscurvey(dot)com |
Cc: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: correlated query as a column and where clause |
Date: | 2011-04-15 15:44:13 |
Message-ID: | 552698.82527.qm@web161501.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)chriscurvey(dot)com>
To: salah jubeh <s_jubeh(at)yahoo(dot)com>
Cc: pgsql <pgsql-general(at)postgresql(dot)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_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!
From | Date | Subject | |
---|---|---|---|
Next Message | salah jubeh | 2011-04-15 15:49:20 | Re: correlated query as a column and where clause |
Previous Message | Chris Curvey | 2011-04-15 15:28:39 | Re: correlated query as a column and where clause |