Re: Make a LOOP function with a delay

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;