Thread: pl/perl setof function

pl/perl setof function

From
Alex -
Date:
Hi,
i have a PL/PERL RETURN SETOF function which processes a few 10k records.
The processing takes quite some time and in order to display progress
I use a return_next after every few thousand records.

However, the function returns all messages when it completes and exists which is not really what i want. I also tried the elog NOTICE but then I only get the messages back to the psql.

I am calling the function from a perl script and would like to catch these progress messages.

Is there any way to solve my problem?

Thanks
Alex


Start searching NOW! Search for properties that match your lifestyle!

Re: pl/perl setof function

From
Andy Colson
Date:
On 1/13/2010 12:20 PM, Alex - wrote:
> Hi,
> i have a PL/PERL RETURN SETOF function which processes a few 10k records.
> The processing takes quite some time and in order to display progress
> I use a return_next after every few thousand records.
>
> However, the function returns all messages when it completes and exists
> which is not really what i want. I also tried the elog NOTICE but then I
> only get the messages back to the psql.
>
> I am calling the function from a perl script and would like to catch
> these progress messages.
>
> Is there any way to solve my problem?
>
> Thanks
> Alex
>

In the doc's you'll see that return_next copies the record into an
in-memory table which is then returned once the storedproc is finished.

Not sure how raise works though.

-Andy

Re: pl/perl setof function

From
Andy Colson
Date:
On 1/13/2010 1:26 PM, Andy Colson wrote:
> On 1/13/2010 12:20 PM, Alex - wrote:
>> Hi,
>> i have a PL/PERL RETURN SETOF function which processes a few 10k records.
>> The processing takes quite some time and in order to display progress
>> I use a return_next after every few thousand records.
>>
>> However, the function returns all messages when it completes and exists
>> which is not really what i want. I also tried the elog NOTICE but then I
>> only get the messages back to the psql.
>>
>> I am calling the function from a perl script and would like to catch
>> these progress messages.
>>
>> Is there any way to solve my problem?
>>
>> Thanks
>> Alex
>>
>
> In the doc's you'll see that return_next copies the record into an
> in-memory table which is then returned once the storedproc is finished.
>
> Not sure how raise works though.
>
> -Andy
>

Humm.. further to that, you could probably catch the notices from perl
with this:

http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_notifies

I'm assuming elog is like raise, and that they get sent immediately and
not when the stored proc is finished.

-Andy