plpgsql function with offset - Postgres 9.1

[Patrick B <patrickbakerbr@xxxxxxxxx>]

Hi guys,

I am running a background task on my DB, which will copy data from tableA to tableB. For that, I'm writing a PL/PGSQL function which basically needs to do the following:

1. Select the data from tableA
2. The limit will be put when calling the function
3. insert the selected data on Step 1 onto new table

Question:

• When I stop it and start it again, how can the query "know" that it has already processed some rows so it won't do it twice on the same rows? If it stopped on row number 100, I need it to continue on row number 101, for example.
  
• How can I ask the function to return the number of processed rows?

I can add a column on TableB if needed, but not on tableA.

This is what I've done so far:

select data_copy(500);

CREATE or REPLACE FUNCTION data_copy(rows integer)

RETURNS SETOF bigint AS $$

declare

        row record;

        offset_num integer;

BEGIN

FOR row IN EXECUTE '

            SELECT

                    id,

                    path,

                    name,

                    name_last,

                    created_at

            FROM

                    tablea

            WHERE

                    ready = true

            ORDER BY 1 LIMIT ' || rows || ' OFFSET ' || rows || ''

LOOP

INSERT INTO tableB (id,path,name,name_last,created_at)

VALUES (row.id,row.path,row.name,row.name_last,row.created_at);

END LOOP;

END

$$ language 'plpgsql';