Re: PL/PGSQL + inserts+updates+limit - Postgres 9.3

[Patrick Baker <patrickbakerbr@xxxxxxxxx>]

2016-06-03 2:10 GMT+12:00 David G. Johnston <david.g.johnston@xxxxxxxxx>:

On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker <patrickbakerbr@xxxxxxxxx> 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 = "">

​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 = "" 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....