Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3 - Mailing list pgsql-general

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@aklaver.com