From: | Patrick Baker <patrickbakerbr(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3 |
Date: | 2016-06-03 03:37:16 |
Message-ID: | CAJNY3it5P5tXPtUu0DfTa2CCmVw_-7qL3d53ScbcPc66fmFFAQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi guys,
-
The function works... All the data is updated as expected. However, when
I call the function for the second time, it touches the rows that had
already been touched by the previous call....
-
It triplicate ( LIMIT 3 ) the records.
*Question:*
How can I make the function to gets the next 3 rows and not use the same
rows that have been used before?
Function updated:
CREATE or REPLACE FUNCTION function_data_1()
RETURNS SETOF bigint AS $$
declare
row record;
BEGIN
-- copying the data to the backup table (not the blobs)-- Limiting in
5000 rows each callFOR row IN EXECUTE '
SELECT
t1.file_id
FROM
table1 t1
JOIN
table3 t3 ON t3.file_id = t1.file_id
ORDER BY 1 LIMIT 3 '
LOOP
-- Creating the backup table with the essential dataINSERT INTO table2
(note_id, size, file_id, full_path)
(
SELECT
t1.note_id,
t1.size,
t1.file_id,
t1.full_path
FROM
table1 t1
JOIN
table3 t3 ON t3.file_id = t1.file_id
WHERE
t1.file_id = row.file_id
);
-- copying the blobs to the table above table2
UPDATE junk.table2 t2 SET data =
(
SELECT
o1.data
FROM
original_table1_b o1
JOIN
table3 t3 ON t3.file_id = o1.file_id
WHERE
t3.migrated = 0
AND
t2.file_id = o1.file_id
AND
o1.file_id = row.file_id
)
WHERE t2.file_id = row.file_id;
-- updating the migrated column from 0 to 1
UPDATE
table3 t2
SET
migrated = 1
WHERE
t2.file_id = row.file_id
AND
migrated = 0;
-- set the blobs as null
UPDATE
original_table1_b o1
SET
data = NULL
WHERE
o1.file_id = row.file_id;END LOOP;
END
$$ language 'plpgsql';
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-06-03 03:50:33 | Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3 |
Previous Message | Michael Paquier | 2016-06-03 02:32:02 | Re: WAL's listing in pg_xlog by some sql query |