PL/PGSQL + inserts+updates+limit - Postgres 9.3

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?