From: | James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Make a LOOP function with a delay |
Date: | 2013-07-24 13:37:18 |
Message-ID: | CAMu32ABBGYqZ8G8c9fnU31Y3j_U1Rt81LAWTkk=fbHqeH7Z8=Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)gmail(dot)com> wrote:
> 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2013-07-24 13:44:30 | Re: Make a LOOP function with a delay |
Previous Message | Merlin Moncure | 2013-07-24 13:21:23 | Re: Make a LOOP function with a delay |