From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Patrick Baker <patrickbakerbr(at)gmail(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 04:31:00 |
Message-ID: | 52f656b8-42b1-cc38-77de-f5bf659ed570@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 06/02/2016 08:37 PM, Patrick Baker wrote:
> 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:
>
> |CREATEorREPLACE FUNCTIONfunction_data_1()RETURNS SETOF bigint
> AS$$declarerowrecord;BEGIN-- copying the data to the backup table (not
> the blobs)-- Limiting in 5000 rows each callFORrowINEXECUTE' 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
> dataINSERTINTOtable2
> (note_id,size,file_id,full_path)(SELECTt1.note_id,t1.size,t1.file_id,t1.full_path
> FROMtable1 t1 JOINtable3 t3 ONt3.file_id =t1.file_id WHEREt1.file_id
> =row.file_id );-- copying the blobs to the table above
> table2UPDATEjunk.table2 t2 SETdata =(SELECTo1.data FROMoriginal_table1_b
> o1 JOINtable3 t3 ONt3.file_id =o1.file_id WHEREt3.migrated
> =0ANDt2.file_id =o1.file_id ANDo1.file_id =row.file_id )WHEREt2.file_id
> =row.file_id;-- updating the migrated column from 0 to 1UPDATEtable3 t2
> SETmigrated =1WHEREt2.file_id =row.file_id ANDmigrated =0;-- set the
> blobs as nullUPDATEoriginal_table1_b o1 SETdata =NULLWHEREo1.file_id
> =row.file_id;ENDLOOP;END$$language 'plpgsql';|
>
> |
> |
"
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 call
FOR 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 data
INSERT 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
);
......."
Are you not repeating yourself, why not?:
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 call
FOR row IN EXECUTE '
SELECT
t1.file_id
t1.size,
t1.file_id,
t1.full_path
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 data
INSERT INTO table2 (row.note_id, row.size, row.file_id, row.full_path)
.....
Still not seeing what the JOIN to table3 t3 gets you?
Any way the function works.
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Dennis | 2016-06-03 04:40:15 | Re: psql remote shell command |
Previous Message | Patrick Baker | 2016-06-03 03:59:43 | Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3 |