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

On 06/01/2016 10:04 PM, Patrick Baker wrote:
>
>
>     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 = NULL 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?

I do not what you are trying to LIMIT/OFFSET, so I have no idea where to
place the LIMIT/OFFSET.

Maybe an example query showing what you are trying to do will help?

>
> Thanks


--
Adrian Klaver
adrian.klaver@aklaver.com