Postgres Pro
Downloads
Home   >   mailing lists

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

From Patrick Baker
Subject Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3
Date
Msg-id CAJNY3it-YFv3mLvG9SwrEb-S3xpHOC9ExoyP3OdyzT-nU_9uBA@mail.gmail.com
Whole thread Raw
In response to Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3
List pgsql-general
Tree view



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 = NULL 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=NULL.

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 = NULL 
        WHERE 
                o1.file_id = row.file_id;
END LOOP;

END

$$ language 'plpgsql';

an.klaver@aklaver.c

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: psql remote shell command
Next
From: Sameer Kumar
Date:
Subject: Re: WAL's listing in pg_xlog by some sql query