Re: [GENERAL] “Loop” in plpgsql Function - PostgreSQL 9.2

[dinesh kumar <dineshkumar02@xxxxxxxxx>]

Hello,

On Mon, Jan 18, 2016 at 1:37 PM, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> wrote:

I've created a function that allows me to do an huge update.

But I need to limit this function. I need to do 50k rows (example) and then stop it. After that I need to continue from the rows that I've stopped... I'll have to modify the call function select batch_number() as well.

How can I do that? Using for?

The function below is already working, but, the table has 40m rows. And it's taking years.

Do you need to run the function on any Staging(Not Production).  I mean, do you want to run this batch processes on a single transaction.

If not, I had the similar problem, where I needed to implement a function, which we can run in multiple sessions. I ran this function in one of the BI servers, where we have around 5 Million records.

Find this link about the implementation details.

If your question was about "Using Loops", then please ignore my comments.

FUNCTION:

CREATE or REPLACE FUNCTION batch_number()
RETURNS INTEGER AS $$

declare
   batch_num integer;
   offset_num integer;
begin
    offset_num = 0;
    batch_num = 1;

    while (select true from gorfs.nfs_data where batch_number is null limit 1) loop
        with ids(id) as
        (
            select
                file_id
            from
                gorfs.nfs_data
            order by
                file_id
            offset offset_num
            limit 1000
        )
        update
            gorfs.nfs_data
        set
            batch_number = batch_num
        from ids
        where file_id = ids.id;

        offset_num = offset_num + 1000;
        batch_num = batch_num + 1;
    end loop;

    return batch_num;
end

$$ language 'plpgsql';

--

Regards,

Dinesh

manojadinesh.blogspot.com