On 06/01/2016 05:10 PM, Patrick Baker wrote:
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*INTOtable3_n_b FROM(SELECTaccount_id,note_id,st_ino,size
FROM(SELECTDISTINCTON(note_id)note_id,MAX(size),file_id,id
FROMtable1_n_b GROUPBYnote_id,size,file_id,id ORDERBYnote_id,size
desc)ASr1 )ASr2;|
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?
I maybe be missing it, but I see no LIMIT in the function.
I do see OFFSET and it looks backwards to me?:
|| $1 ||' offset '||
https://www.postgresql.org/docs/9.5/static/sql-select.html
LIMIT Clause
The LIMIT clause consists of two independent sub-clauses:
LIMIT { count | ALL }
OFFSET start
Also I not sure what offset_num is supposed to do, it is declared but
not used?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
