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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
