Re: Subselects - recursion problem - Mailing list pgsql-general

From Philip Rhoades
Subject Re: Subselects - recursion problem
Date
Msg-id 1117455752.14509.26.camel@phil.chu.com.au
Whole thread Raw
In response to Re: Subselects - recursion problem  (Martijn van Oosterhout <kleptog@svana.org>)
List pgsql-general
Martijn,


On Mon, 2005-05-30 at 21:08, Martijn van Oosterhout wrote:
> On Mon, May 30, 2005 at 08:32:15PM +1000, Philip Rhoades wrote:
> > Martijn,
> >
> >
> > On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote:
> > > "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 . .
>
> Not really, if you think about it. SQL is a form of relational algebra
> and like in normal algebra it's not unusual to have various variables
> relating to eachother in various ways. Some expressions may look
> recursive, but that's just another relationship.


OK.


> As you can see from the query plan, it scans through each record in c1
> filtering out rows based on the easy conditions. It then works out the
> subquery for each row and compares the result with policy. If it works
> the row is returned otherwise it keeps going. There's no recursion.


OK, makes sense in English . .


> Perhaps the easiest way to think about it is having the subquery as a
> function, and write it like:
>
> SELECT c1.loc, c1.lob, c1.policy
> FROM crec AS c1
> WHERE c1.t_type = '1'
> AND c1.t_diss = '2'
> AND c1.recon = 'Y'
> AND c1.policy = Subquery( c1 )
> ORDER BY c1.loc, c1.lob, c1.policy ;
>
> That doesn't look recursive to me, yet it's the same thing...
>
> Hope this helps,


No, that looks just as recursive to me but I get the picture from the
English explanation so thanks again.

Regards,

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@chu.com.au



pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: Subselects - recursion problem
Next
From: Alexandre Lollini
Date:
Subject: Problem with void integer