[SQL] Need a sample Postgre SQL script
Hi All, I'm kind of new to Postgre and I need some advice. 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. I could figure out and write the logic for this. I only need a little bit similar sample script so that I can figure out Postgre syntax. Like - Loop syntax to through all the rows return by a select * - How to assign that value to a parameter - using that parameter in the Insert statement - etc ... Thanks, Dhanushka.
Re: [SQL] Need a sample Postgre SQL script
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 >
Re: [SQL] Need a sample Postgre SQL script
Worked perfectly. Thanks Richard. On Tue, Jul 1, 2008 at 10:17 AM, Richard Huxton <[EMAIL PROTECTED]> 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 >
