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

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