2016-06-03 2:10 GMT+12:00 David G. Johnston <david.g.johns...@gmail.com>:
> On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker <patrickbake...@gmail.com> > wrote: > >> >>>> It's all working, except the LIMIT... if possible can you please give >> me an example of that LIMIT in some of those queries? >> >> > You also should use ORDER BY when using LIMIT and OFFSET; though depending > on the setup it could be omitted. Usually as long as the second execution > cannot select any of the records the first execution touched you can choose > a random quantity. But if you want random then using OFFSET is pointless. > > SELECT * > FROM generate_series(1, 10) > ORDER BY 1 > LIMIT 5 > OFFSET 3 > > generate_series > ---------------------- > 4 > 5 > 6 > 7 > 8 > > You are going to have difficultly finding people willing to help when you > cannot put together a self-contained and syntax error free example (I think > the last one is...) of what you want to do. The PostgreSQL parser is very > good at reading code and telling you what it doesn't like. I'm not > inclined to spend time reading queries that obviously cannot run and point > out those same problems. If you can a particular error you don't > understand I'll be happy to try and explain what it is trying to tell you. > > You probably need to reformulate your update to read: > > UPDATE tbl > FROM ( > SELECT 50 RECORDS > ) src > WHERE src = tbl; > > And ensure that the 50 being selected each time through are a different > 50. > > Writeable CTEs will probably help here. > > https://www.postgresql.org/docs/current/static/queries-with.html > > David J. > > 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. 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 ); 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 $$ 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....
