From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Patrick Baker <patrickbakerbr(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3 |
Date: | 2016-06-03 00:32:45 |
Message-ID: | 6a5e4fc2-3bd7-5390-6e3a-d3e00e09bc9b@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 06/02/2016 02:03 PM, Patrick Baker wrote:
>
>
> 2016-06-03 2:10 GMT+12:00 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com
> <mailto:david(dot)g(dot)johnston(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2016-06-03 00:56:36 | Re: psql remote shell command |
Previous Message | David G. Johnston | 2016-06-03 00:10:09 | Re: psql remote shell command |