Re: Need a sample Postgre SQL script

Worked perfectly.
Thanks Richard.

On Tue, Jul 1, 2008 at 10:17 AM, Richard Huxton <dev(at)archonet(dot)com> wrote:

> Dhanushka Samarakoon wrote:
>
>> Thanks for the reply.
>> But one problem I have is I need to loop through all the rows in the table
>> and in each iteration I need to fetch the value of mydate in to a variable
>> and split it to month and year and add two rows with *value, 91, month* (2
>> ,
>> 91, Augest) and *value, 86, year* (2 , 86 , 2009)
>>
>> So I need an idea on
>> - how to loop through all the rows thats returned by a select statement.
>> - for each statement how to get the value of mydate in to a variable, so
>> that I can use SubString to split it in to date and year and use them in
>> the
>> insert statement.
>>
>
> Ah, looking back I see "mydate" isn't actually a date. Note the space in
> the pattern for substring() below:
>
> INSERT INTO metadata (value, field, mydate)
> SELECT value,91, substring(mydate, '(.+) ')
> FROM metadata
> UNION ALL
> SELECT value, 86, substring(mydate, ' (.+)')
> FROM metadata;
>
> Does that do it for you? Try the SELECT clauses by themselves to check if
> they're doing the right thing.
>
> --
> Richard Huxton
> Archonet Ltd
>