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;