Re: Need a sample Postgre SQL script - Mailing list pgsql-sql

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