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. On Tue, Jul 1, 2008 at 9:42 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: > Dhanushka Samarakoon wrote: > >> Hi All, >> >> I'm kind of new to Postgre and I need some advice. >> > > No problem. It's PostgreSQL or Postgres by the way. > > I have the following table. >> metadata (value:integer , field:integer , mydate:text) >> >> given below is a sample record from that. >> ( 2 , 16 , Augest 2009) >> >> I need a script that will read the above table and for each such row it >> will >> insert two rows as below. >> >> ( 2 , 91 , Augest ) >> ( 2 , 86 , 2009 ) >> >> 16, 91 and 86 are static values. *value and field together* creates the >> primary key. >> > > CREATE TEMP TABLE staticfields (f integer); > INSERT INTO staticfields VALUES (91); > INSERT INTO staticfields VALUES (86); > > INSERT INTO metadata (value, field, mydate) > SELECT value, f, mydate > FROM metadata, staticfields; > > -- > Richard Huxton > Archonet Ltd >
