>
>
>>
>
> Why are you joining to table3_nb?
> You do not use any fields from it.
>
> How do you know what data in table1_n_b to get?
> I see this grabbing the same information over and over again.
SELECT * INTO table3_n_b FROM (
SELECT account_id, note_id, file_id FROM
(
SELECT DISTINCT ON
(note_id) note_id,
MAX(size),
file_id,
company_id
FROM
table1_n_b
GROUP BY
note_id, size, file_id, company_id
ORDER BY
note_id, size desc
) AS r1) AS r2;
Because I just wanna touch the greatest file_id ( by size ) of each note_id
And the file_id I must change is into the table3
That's why:
table3_n_b t3 ON t3.file_id = t1.file_id
>
>
>>
>> UPDATE table2_y_b t2 SET segment_data =
>>
>> (
>>
>> SELECT
>>
>> o1.data
>>
>> FROM
>>
>> original_table1_b o1
>>
>> JOIN
>>
>> table3_n_b t3 ON t3.file_id = o1.file_id
>>
>> WHERE
>>
>> t2.migrated = 0
>>
>> AND
>>
>> t2.file_id = o1.file_id
>>
>> );
>>
>>
>> UPDATE table2_y_b SET migrated = 1 WHERE file_id =
>> crtRow.file_id AND migrated = 0;
>>
>>
>> UPDATE original_table1_b SET data = NULL WHERE file_id =
>> crtRow.file_id;
>>
>
> All the above would seem to be handled in a LOOP.
> Grab the data from:
>
> SELECT
>
> t1.note_id,
>
> t1.size,
>
> t1.file_id,
>
> t1.full_path
>
> FROM
>
> table1_n_b t1
>
> with suitable WHERE clause and use:
>
>
> https://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
Hmm ok... but...
INSERT INTO table2_y_b (note_id, size, file_id, full_path)
(
SELECT
t1.note_id,
t1.size,
t1.file_id,
t1.full_path
FROM
table1_n_b t1
JOIN
table3_n_b t3 ON t3.file_id = t1.file_id
);
I don't need anything else on the WHERE clause , as the *ON t3.file_id =
t1.file_id* is already doing what I need.... ( and it works.. I tested it )
>
>
> to iterate over the results. As part of the iteration do your INSERT and
> UPDATE using the RECORD.file_id. This includes setting migrated=1 and
> data=NULL.
>
Yep.. that's the way I started by doing this...
Can you please tell me if this would be right?
CREATE or REPLACE FUNCTION function_data_1()
RETURNS INTEGER 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 '
INSERT INTO table2_y_b (note_id, size, file_id, full_path)
(
SELECT
t1.note_id,
t1.size,
t1.file_id,
t1.full_path
FROM
table1_n_b t1
JOIN
table3_n_b t3 ON t3.file_id = t1.file_id
ORDER BY 1
LIMIT 5000
)'
LOOP
-- copying the blobs to the table above
UPDATE table2_y_b t2 SET segment_data =
(
SELECT
o1.data
FROM
original_table1_b o1
JOIN
table3_n_b t3 ON t3.file_id = o1.file_id
WHERE
t2.migrated = 0
AND
t2.file_id = o1.file_id
)
WHERE t2.file_id = row.file_id
END LOOP;
-- updating the migrated column from 0 to 1
LOOP
UPDATE
table2_y_b t2
SET
migrated = 1
WHERE
t2.file_id = row.file_id
AND
migrated = 0
END LOOP;
LOOP
UPDATE
original_table1_b o1
SET
data = NULL
WHERE
o1.file_id = row.file_id;
END LOOP;
END
$$ language 'plpgsql';
an.klaver@aklaver.c <adrian.kla...@aklaver.com>
