Re: Need a sample Postgre SQL script

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