On 06/02/2016 02:03 PM, Patrick Baker wrote:
2016-06-03 2:10 GMT+12:00 David G. Johnston <david.g.johns...@gmail.com
<mailto:david.g.johns...@gmail.com>>:
Hi David.
The SQLs inside the function works.... I'm just having problem about
limiting the query to the number of rows I want, and also, to teach the
update SQL to only touch the records the other SQLs inside the function
have touched.
See notes inline.
This is the function updated:
CREATE or REPLACE FUNCTION function_data_1(rows integer)
RETURNS INTEGER AS $$
declare
completed integer;
offset_num integer;
crtRow record;
BEGIN
offset_num = 0;
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
);
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.
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
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.
END
$$ language 'plpgsql';
- As you can see, the first *insert*, inserts data into a new table from
another select. This query must be limited by the number of rows I'll
provide when calling the function; example:
select function_data_1(5000);
select function_data_1(60000);
select function_data_1(15000);
- The first *update*, copies the BLOBS from the original_table1_b table
into the new one (as above). Here, I also need the query knows to only
touch those records that have been touched by the above query.
- The second *update*, set the table2_y_b.migrated column from 0 to 1,
telling me that, that record has been touched by the query. So the next
call ( select function_data_1(60000); ) will already know that it does
not need to touch that record; example:
WHERE
t2.migrated = 0
- The third and last *update*, deletes (set the blobs column as null)
the blobs that have already been touched by the above queries....
Still.. don't know how to tell postgres to only touches the rows that
have been touched by the above queries....
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
