Re: Subselects - recursion problem

Martijn,

On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote:
> On Mon, May 30, 2005 at 07:33:04PM +1000, Philip Rhoades wrote:
> > People,
> >
> > The following script works (I have confirmed it by doing two separate
> > views and doing a select on them) - but I don't understand why there
> > isn't a recursion problem with c1.policy and c2.policy - is there some
> > sort of trick happening?
>
> "recursion problem" ? It's called a correlated subquery. SQL is
> declarative, you state what you want and the database figures out how
> to get the answer for you. I think you need to go and read up on the
> basics of SQL.

Declarative or not, it looks strange having the output of the first
select dependent on a second select, which is dependent on the output of
the first select . .

> If you want to see *how* the database is working out the answer, use
> explain and it'll display the query plan.

Sort (cost=402711.95..402711.96 rows=1 width=20)
Sort Key: loc, lob, policy
-> Index Scan using crec_9 on crec c1 (cost=0.00..402711.94 rows=1
width=20)
Index Cond: (t_diss = '2'::bpchar)
Filter: ((t_type = '1'::bpchar) AND (recon = 'Y'::bpchar) AND
(policy = (subplan)))
SubPlan
-> Index Scan using crec_1 on crec c2 (cost=0.00..6.01
rows=1 width=10)
Index Cond: (($0 = loc) AND ($1 = lob) AND ($2 =
policy))
Filter: ((t_type = '1'::bpchar) AND (t_diss =
'0'::bpchar) AND (recon = 'N'::bpchar))

I can see that PG doesn't use "policy" in the filter of the subplan at
all but that doesn't help very much . .

Thanks anyway.

Phil.
--
Philip Rhoades

Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275)
GPO Box 3411
Sydney NSW 2001
Australia
Mobile: +61:0411-185-652
Fax: +61:2:8923-5363
E-mail: phil(at)chu(dot)com(dot)au