Re: looping over a small record set over and over in a function

On Jun 19, 2009, at 8:23 PM, Ivan Sergio Borgonovo wrote:
> I've a record set on which I have to loop several times.
> The function should return the same record set + one more computed
> field.
> Something that in could look like:
>
> foreach(row) {
>  // compute stuff
>  if(...) {
>  }
>  // place stuff in field[N+1] of the row
> }
> if(some condition) {
>  //
> }
> foreach(row) {
>  // compute stuff
>  if(...) {
>  }
>  // place stuff in a field[N+1] of the row
> }
> if(some condition) {
>  //
> }
> ...
>
> actually return row + computed field.
>
> in pgplsql
>
> where each loop depends on the result of the previous.
> The dataset is very small.
>
>
> If I could easily load all the dataset into an array, loop through
> it and then just update the computed field it would be nice... but
> how?

You could add a column to your query as a placeholder for the computed
value.
For example, SELECT *, 0 AS computed_value FROM table.

If you use a scrollable cursor (possible in PL/pgSQL these days,
although it still has some limitations) you could just loop through
its results and rewind the cursor until you're done.

It does look like you're implementing some kind of aggregate function
though. Maybe you can implement it as an actual aggregate function,
maybe in combination with the windowing stuff in 8.4? Then you could
just add the aggregate to your query instead of using your query
results in a function. That's about the inverse of what you're
attempting now.
You seem to be implementing something that would look like SELECT
sum(SELECT * FROM table), while you may be better off aiming for
SELECT SUM(value) FROM table.

Considering you want to loop over the results multiple times that may
not be possible; only you can tell.

> Is it really worth to load the whole record set in an array, loop
> over etc... in spite of eg. building a temp table with the same
> structure of the input record set + 1 field, loop over the table
> etc... what about all the UPDATEs involved to change field N+1 of
> the temp table? Will be they expensive?

Neither of those look like a convenient approach. With the array you
lose all the meta-data the record set provided (types, column names,
etc), while the temp table approach looks like it will be kind of
inefficient.

Is the result of that function volatile or can you maybe store it
after computing it once and fire a trigger to update the computed
value if the underlying data changes?

I wonder why you need to re-iterate over your result set multiple
times? It's kind of rare that once isn't enough.

And lastly, if your current approach really is the only way to compute
what you're after, then maybe PL/pgSQL isn't the right match for the
problem; it looks like you'd be better served by a language that can
work with arrays of typed structures. As I'm not familiar with the
other PL languages I can't tell whether they would be suitable in that
respect, but I suspect Python or Java would be able to handle this
better.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a3cba54759154137769037!