Обсуждение: Make a LOOP function with a delay

Hi there,

I have a query as below which works great and does exactly what I want
it too. It has this form:

SELECT my_function(
field_one,
field_two,
field_three,
field_four
)
FROM my_table
WHERE row_number = 1;

However I want to make it loop and run the function for each row of
my_table. I'd also like to put a delay in it. But I'm not quite sure
how. The pseudo-code would be:

1) Take the first row from the table
2) Run the function with the row number in the WHERE clause
3) Pause for 5 seconds
4) Move to the next row
5) Run the function again
etc... until the whole table is done.

I'd really appreciate some guidance please. I've looked at loops in
the manual but it's a steep learning curve...

Thanks

James

On Wed, Jul 24, 2013 at 6:44 AM, James David Smith
<james.david.smith@gmail.com> wrote:
> Hi there,
>
> I have a query as below which works great and does exactly what I want
> it too. It has this form:
>
> SELECT my_function(
> field_one,
> field_two,
> field_three,
> field_four
> )
> FROM my_table
> WHERE row_number = 1;
>
> However I want to make it loop and run the function for each row of
> my_table. I'd also like to put a delay in it. But I'm not quite sure
> how. The pseudo-code would be:
>
> 1) Take the first row from the table
> 2) Run the function with the row number in the WHERE clause
> 3) Pause for 5 seconds
> 4) Move to the next row
> 5) Run the function again
> etc... until the whole table is done.
>
> I'd really appreciate some guidance please. I've looked at loops in
> the manual but it's a steep learning curve...

SQL best practice is generally to avoid loops.  one way to do that:

CREATE OR REPLACE FUNCTION Notice(msg TEXT) RETURNS VOID AS
$$
BEGIN
  RAISE NOTICE '[%] %', clock_timestamp()::timestamp(2), msg;
END;
$$ LANGUAGE plpgsql;

SELECT my_function(
  field_one,
  field_two,
  field_three,
  field_four
 ),
 notice('did row ' || row_number),
 pg_sleep(.1)
FROM my_table;

Hi Merlin,

Just been messing about this with. I'm a bit confused but trying to
understand it. When I implement it, I'm told that the column
'row_number' isn't recognised. I guessed that you meant to write
row_number() so tried that, but then it wants an OVER clause.

Also should the line

notice('did row ' || row_number)

Actually have a capital N at the start to match the function we've made?

Thanks

James

On 24 July 2013 14:21, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Jul 24, 2013 at 6:44 AM, James David Smith
> <james.david.smith@gmail.com> wrote:
>> Hi there,
>>
>> I have a query as below which works great and does exactly what I want
>> it too. It has this form:
>>
>> SELECT my_function(
>> field_one,
>> field_two,
>> field_three,
>> field_four
>> )
>> FROM my_table
>> WHERE row_number = 1;
>>
>> However I want to make it loop and run the function for each row of
>> my_table. I'd also like to put a delay in it. But I'm not quite sure
>> how. The pseudo-code would be:
>>
>> 1) Take the first row from the table
>> 2) Run the function with the row number in the WHERE clause
>> 3) Pause for 5 seconds
>> 4) Move to the next row
>> 5) Run the function again
>> etc... until the whole table is done.
>>
>> I'd really appreciate some guidance please. I've looked at loops in
>> the manual but it's a steep learning curve...
>
> SQL best practice is generally to avoid loops.  one way to do that:
>
> CREATE OR REPLACE FUNCTION Notice(msg TEXT) RETURNS VOID AS
> $$
> BEGIN
>   RAISE NOTICE '[%] %', clock_timestamp()::timestamp(2), msg;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT my_function(
>   field_one,
>   field_two,
>   field_three,
>   field_four
>  ),
>  notice('did row ' || row_number),
>  pg_sleep(.1)
> FROM my_table;

On Wed, Jul 24, 2013 at 8:37 AM, James David Smith
<james.david.smith@gmail.com> wrote:
> Hi Merlin,
>
> Just been messing about this with. I'm a bit confused but trying to
> understand it. When I implement it, I'm told that the column
> 'row_number' isn't recognised. I guessed that you meant to write
> row_number() so tried that, but then it wants an OVER clause.
>
> Also should the line
>
> notice('did row ' || row_number)
>
> Actually have a capital N at the start to match the function we've made?

<plz try to avoid top posting>

Notice should have been capitalized -- but will work fine.   postgres
folds all non-quoted identifiers to lower case.

row_number came from your example:
> WHERE row_number = 1;

the idea is that instead of picking a particular row of the table, we
are going to do all the rows.

merlin