Hi guys,
I need a function ( PL/PGSQL ) to perform a deletion of some BLOBS...
I have four tables:
*- original_table1_b =* Original table, where the BLOBS are
> *- table1_n_b =* Table where everything related to the BLOBS is stored
> (file_id, account_id, note_id, etc)
> *- table2_y_b =* Table BACKUP - The blobs+data will be copied to here
> before being deleted
> *- table3_n_b =* On the *table1_n_b*, each blob is related to a note_id.
> Each note_id has three different file_id. I want to delete just the
> greatest one. So on this *table3_n_b* table I'm storing the greates
> file_id (by size)
How is the *table3_n_b* table created:
SELECT * INTO table3_n_b FROM(
SELECT account_id, note_id, st_ino, size FROM
(
SELECT DISTINCT ON
(note_id) note_id,
MAX(size),
file_id,
id
FROM
table1_n_b
GROUP BY
note_id, size, file_id, id
ORDER BY
note_id, size desc
) AS r1) AS r2;
The function must perform the following:
1 - Select *note_id + size + file_id + full_path* from *table1_n_b* table
to the new *table2_y_b* one, but only those file_id that are greatest, so
here we use the table created above: *table3_n_b*:
- Something like this?
INSERT INTO table2_y_b (note_id, size, file_id, full_path)
> (
> SELECT
> t1.note_id,
> t1.size,
> t1.file_id,
> t1.full_path
> INTO
> table2_y_b
> FROM
> table1_n_b t1
> JOIN
> table3_n_b t3 ON t3.file_id = t1.file_id
> )
2 - Once the Blob's data is inside the *table2_y_b* table, we can now copy
the blobs into the same table.
- something like this?
INSERT INTO table2_y_b (data)
> (
> SELECT
> o1.data
> FROM
> original_table1_b o1
> JOIN
> table3_n_b t3 ON t3.file_id = o1.file_id
> )
3 - Changing the table2_y_b.migrated column from 0 to 1 (1 means the blob
has been already copied):
> FOR crtRow IN execute
> 'UPDATE table2_y_b SET migrated = 1 WHERE file_id = crtRow.file_id AND
> migrated = 0 ' || $1 ||' offset '||
4 - After we have a backup of the blobs+data, we can now delete the blob
(setting the column as NULL)
> FOR crtRow IN execute
'UPDATE original_table1_b SET data = NULL WHERE file_id = crtRow.file_id '
> || $1 ||' offset '||
*This is what I've done so far:*
CREATE or REPLACE FUNCTION function_1_name(rows integer)
RETURNS INTEGER AS $$
declare
completed integer;
crtRow record;
BEGIN
offset_num = 0;
-- Copiyng the data into the table which will store the data+blobs
FOR crtRow 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
INTO
table2_y_b
FROM
table1_n_b t1
JOIN
table3_n_b t3 ON t3.file_id = t1.file_id
) ' || $1 ||' offset '||
-- Copying the BLOBS
FOR crtRow IN execute
'INSERT INTO table2_y_b (data)
(
SELECT
o1.data
FROM
original_table1_b o1
JOIN
table3_n_b t3 ON t3.file_id = o1.file_id
JOIN
table2_y_b t2 ON t2.file_id = o1.file_id
WHERE
t2.migrated = 0
) ' || $1 ||' offset '||
-- Update the migrated column from 0 to 1, for those rows that have been
modified/copied.
FOR crtRow IN execute
'UPDATE table2_y_b SET migrated = 1 WHERE file_id = crtRow.file_id AND
migrated = 0 ' || $1 ||' offset '||
FOR crtRow IN execute
'UPDATE original_table1_b SET data = NULL WHERE file_id = crtRow.file_id '
|| $1 ||' offset '||
RETURN file_id;
END
$$ language 'plpgsql';
Am I doing right?
When I will call the function: *select function_1_name(5000) or **select
function_1_name(15000)* will it respect the limited by the rows?
