Re: Make a LOOP function with a delay

On Wed, Jul 24, 2013 at 6:44 AM, James David Smith
<james(dot)david(dot)smith(at)gmail(dot)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;