Re: Having a plpgsql function return multiple rows that indicate its progress in a cursor like fashion

On 16 Feb 2010, at 1:04, Peter Geoghegan wrote:

> Hello,
>
> At the moment my pg client application (running 8.4) transfers data
> from several remote DBs (that run 8.3) via dblink, using cursors where
> appropriate, and reporting back progress to users using a progress bar
> and brief messages.
>
> I thought it would be great to change my code to have all this done
> within a single plpgsql function, and have all the benefits that that
> brings.
>
> It's easy enough to do all this, by writing a function that RETURNS
> TABLE(progress integer, message text), and RETURNing NEXT when time
> comes to display a new message or increment the progress bar. However,
> that approach has the considerable drawback of not actually returning
> any rows until it finally returns all of them. I'm not willing to give
> up giving the user those messages and having their progress bar
> updated in real-time though. I would like to have the function behave
> as a cursor, and return one row at a time when control reaches each
> RETURN NEXT statement.

I'd think RETURN NEXT would behave the way you want it to. There's probably something in your function causing your function to behave like it does now. I suspect the problem lies in the way you determine how far you've progressed, but you didn't tell us anything about your function, so I'm just guessing.

I don't know much about dblink, so it is possible your problem is related to that. I imagine it may batch "small" result sets and send them over all at once to reduce traffic. Do you see this problem with larger result sets (say >10k rows)?

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4b7a5d9710441627593049!