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

Patrick Baker <patrickbakerbr@xxxxxxxxx> · Thu, 2 Jun 2016 12:10:55 +1200

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 = "" 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 = "" 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?