| 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: | Whole Thread | Raw Message | 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
| 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 |