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

Patrick Baker <patrickbakerbr@xxxxxxxxx> · Fri, 3 Jun 2016 13:17:45 +1200

Why are you joining to table3_nb?

You do not use any fields from it.

How do you know what data in table1_n_b to get?

I see this grabbing the same information over and over again.

SELECT * INTO table3_n_b FROM 
(
        SELECT account_id, note_id, file_id FROM 
        (
        SELECT DISTINCT ON 
                (note_id) note_id, 
                MAX(size),
                file_id,
                company_id
        FROM 
                table1_n_b
        GROUP BY 
                note_id, size, file_id, company_id
        ORDER BY 
                note_id, size desc
        ) AS r1
) AS r2;

Because I just wanna touch the greatest file_id ( by size ) of each note_id
And the file_id I must change is into the table3

That's why:

table3_n_b t3 ON t3.file_id = t1.file_id

            UPDATE table2_y_b t2 SET segment_data =

                (

                        SELECT

                                o1.data

                        FROM

                                original_table1_b o1

                        JOIN

                                table3_n_b t3 ON t3.file_id = o1.file_id

                        WHERE

                                t2.migrated = 0

                        AND

                                t2.file_id = o1.file_id

                );

            UPDATE table2_y_b SET migrated = 1 WHERE file_id =

            crtRow.file_id AND migrated = 0;

            UPDATE original_table1_b SET data = "" WHERE file_id =

            crtRow.file_id;

All the above would seem to be handled in a LOOP.

Grab the data from:

        SELECT

                            t1.note_id,

                            t1.size,

                            t1.file_id,

                            t1.full_path

                    FROM

                            table1_n_b t1

with suitable WHERE clause and use:

https://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

Hmm ok...  but...

INSERT INTO table2_y_b (note_id, size, file_id, full_path)
    (
            SELECT
                    t1.note_id,
                    t1.size,
                    t1.file_id,
                    t1.full_path
            FROM
                    table1_n_b t1
            JOIN
                    table3_n_b t3 ON t3.file_id = t1.file_id
    );

I don't need anything else on the WHERE clause , as the ON t3.file_id = t1.file_id is already doing what I need....  ( and it works.. I tested it )

to iterate over the results. As part of the iteration do your INSERT and UPDATE using the RECORD.file_id. This includes setting migrated=1 and data="">

Yep.. that's the way I started by doing this... 

Can you please tell me if this would be right?

CREATE or REPLACE FUNCTION function_data_1()
RETURNS INTEGER AS $$

declare
        row record;

BEGIN

-- copying the data to the backup table (not the blobs)
-- Limiting in 5000 rows each call
FOR row 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
            FROM
                    table1_n_b t1
            JOIN
                    table3_n_b t3 ON t3.file_id = t1.file_id
ORDER BY 1
LIMIT 5000
    )'

LOOP
-- copying the blobs to the table above
        UPDATE table2_y_b t2 SET segment_data =
        (
            SELECT
                    o1.data
            FROM
                    original_table1_b o1
            JOIN
                    table3_n_b t3 ON t3.file_id = o1.file_id
            WHERE
                    t2.migrated = 0
            AND
                    t2.file_id = o1.file_id
        )
        WHERE t2.file_id = row.file_id
END LOOP;

-- updating the migrated column from 0 to 1
LOOP
        UPDATE 
                table2_y_b t2
        SET 
                migrated = 1 
        WHERE 
                t2.file_id = row.file_id 
        AND 
                migrated = 0
END LOOP;

LOOP
        UPDATE 
                original_table1_b o1
        SET 
                data = "">
        WHERE 
                o1.file_id = row.file_id;
END LOOP;

END

$$ language 'plpgsql';

an.klaver@aklaver.c