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?
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 );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;END
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
);
UPDATE table2_y_b t2 SET segment_data =
o1.data
original_table1_b o1
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;
END
pgsql-general by date:
Соглашаюсь с условиями обработки персональных данных
