correlated query as a column and where clause

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_'

what you are looking for is the having clause

Select nspname, count(1)
FROM pg_tables
where nspname !~* 'pg_'
group by nspname
having count(1) >10

best wishes

Harald

--
Harald Armin Massa www.2ndQuadrant.com
PostgreSQL Training, Services and Support

2ndQuadrant Deutschland GmbH
GF: Harald Armin Massa
Amtsgericht Stuttgart, HRB 736399

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

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@chriscurvey.com>
To: salah jubeh <s_jubeh@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_jubeh@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!

Hello Harald,

Danke ! . My concern is why I get error undefiend attribute if I used the alias
in the where clause

Regards

________________________________
From: Harald Armin Massa <harald@2ndQuadrant.com>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Fri, April 15, 2011 5:26:45 PM
Subject: Re: [GENERAL] correlated query as a column and where clause

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_'

what you are looking for is the having clause

Select nspname, count(1)
FROM pg_tables
where nspname !~* 'pg_'
group by nspname
having count(1) >10

best wishes

Harald

--
Harald Armin Massa www.2ndQuadrant.com
PostgreSQL Training, Services and Support

2ndQuadrant Deutschland GmbH
GF: Harald Armin Massa
Amtsgericht Stuttgart, HRB 736399

salah jubeh <s_jubeh@yahoo.com> writes:

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

The select list can only be computed after the where clause has filtered
the rows, so such a thing would be circular.

regards, tom lane

Hello Tom

Sorry, but I did not get you. I know that the filtering in the where clause and
can be pushed up or down in the parsing tree depending on the optimizer. So in
this certain case, the result could be computed first and filtered by the where
clause later.

Regards

________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: chris@chriscurvey.com; pgsql <pgsql-general@postgresql.org>
Sent: Fri, April 15, 2011 5:49:35 PM
Subject: Re: [GENERAL] correlated query as a column and where clause

salah jubeh <s_jubeh@yahoo.com> writes:

But, why I can not use the alias of the select statement ( as in the original

post) in the where clause.

The select list can only be computed after the where clause has filtered
the rows, so such a thing would be circular.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general