Re: multiple lookup per row

Gary,

> I had the same problem the first time I went from writing sequential
> batch 
> mainframe apps to event-driven interactive windows apps.  Different
> mindset 
> completely.

I grapple with the procedural <--> declarative switchover all the time.
In a way, procedural is easier, since set theory does not come
intuitively to any humans I know.  In my biggest pgsql app there's quite
a number of things I did with LOOPs that could have been done with
properly structured queries.


> BTW, which would run fastest, or would there be much difference? (I
> would 
> have thought not much as the plpgsql function still has to do the
> same select 
> as your sub-selects are.)

Yes, but plpgsql does the selects one at a time, evaluates the response,
and proceeds on to the next.  The declarative statement does them all in
batches, as the SQL engine is designed to do.  Plus my way and Tom's way
can be built as views, allowing further optimization.  Not to mention
that pure SQL will always be faster than even a trusted language.

In other words, my and Tom's queries should run about 50 times as fast
as the procedural method for large tables with indexes.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco