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

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(dot)klaver(at)aklaver(dot)com