Function with limit and offset - PostgreSQL 9.3

From: marcinha rocha <marciaestefanidarocha(at)hotmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Function with limit and offset - PostgreSQL 9.3
Date: 2017-06-09 00:53:38
Message-ID: CY1PR18MB0490632A9A73E64973F66D6BAFCE0@CY1PR18MB0490.namprd18.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi guys! I have the following queries, which will basically select data, insert it onto a new table and update a column on the original table.

CREATE or REPLACE FUNCTION migrate_data()
RETURNS integer;

declare
row record;

BEGIN

FOR row IN EXECUTE '
SELECT
id
FROM
tablea
WHERE
mig = true
'
LOOP

INSERT INTO tableb (id)
VALUES (row.id);

UPDATE tablea a SET migrated = yes WHERE a.id = row.id;

END LOOP;

RETURN numrows; -- I want it to return the number of processed rows

END

$$ language 'plpgsql';

When I call the function, it must execute 2000 rows and then stop. Then when calling it again, it must start from 2001 to 4000, and so on.

How can I do that? I couldn't find a solution for this..

Thanks!
Marcia

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2017-06-09 01:05:47 Re: Function with limit and offset - PostgreSQL 9.3
Previous Message Éric 2017-06-08 20:41:41 Re: Performance issue with Pointcloud extension