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

[Patrick Baker <patrickbakerbr@xxxxxxxxx>]

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?

Yep.. it's declared but it's not used..

Hmm.. interesting that about the LIMIT clause

However.. I had to changed the function...

Here is the new one:

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';

It's all working, except the LIMIT...  if possible can you please give me an example of that LIMIT in some of those queries?

Thanks