Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3
От | Adrian Klaver |
---|---|
Тема | Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3 |
Дата | |
Msg-id | 6a5e4fc2-3bd7-5390-6e3a-d3e00e09bc9b@aklaver.com обсуждение исходный текст |
Ответ на | Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3 (Patrick Baker <patrickbakerbr@gmail.com>) |
Ответы |
Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3
(Patrick Baker <patrickbakerbr@gmail.com>)
|
Список | pgsql-general |
On 06/02/2016 02:03 PM, Patrick Baker wrote: > > > 2016-06-03 2:10 GMT+12:00 David G. Johnston <david.g.johnston@gmail.com > <mailto:david.g.johnston@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.klaver@aklaver.com
В списке pgsql-general по дате отправления: