Thread: Need a sample Postgre SQL script

Hi All,<br /><br />I'm kind of new to Postgre and I need some advice.<br />I have the following table.<br />metadata
(value:integer, field:integer , mydate:text)<br /><br />given below is a sample record from that.<br />( 2 , 16 ,
Augest2009)<br /><br />I need a script that will read the above table and for each such row it will insert two rows as
below.<br/><br />( 2 , 91 , Augest )<br />( 2 , 86 , 2009 )<br /><br />16, 91 and 86 are static values. <u>value and
fieldtogether</u> creates the primary key.<br /><br />I could figure out and write the logic for this. I only need a
littlebit similar sample script so that I can figure out Postgre syntax.<br />Like <br />- Loop syntax to through all
therows return by a select *<br />- How to assign that value to a parameter<br /> - using that parameter in the Insert
statement<br/>- etc ...<br /><br />Thanks,<br />Dhanushka.<br /><br /> 

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

Thanks for the reply.<br />But one problem I have is I need to loop through all the rows in the table and in each
iterationI need to fetch the value of mydate in to a variable and split it to month and year and add two rows with
<b>value,91, month</b> (2 , 91, Augest) and <b>value, 86, year</b> (2 , 86 , 2009)<br /><br />So I need an idea on <br
/>-how to loop through all the rows thats returned by a select statement.<br />- for each statement how to get the
valueof 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.<br/><br /><div class="gmail_quote">On Tue, Jul 1, 2008 at 9:42 AM, Richard Huxton <<a
href="mailto:dev@archonet.com">dev@archonet.com</a>>wrote:<br /><blockquote class="gmail_quote" style="border-left:
1pxsolid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">Dhanushka Samarakoon
wrote:<br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"> Hi All,<br /><br /> I'm kind of new to Postgre and I need some advice.<br /></blockquote><br
/></div>No problem. It's PostgreSQL or Postgres by the way.<div class="Ih2E3d"><br /><br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> I
havethe following table.<br /> metadata (value:integer , field:integer , mydate:text)<br /><br /> given below is a
samplerecord from that.<br /> ( 2 , 16 , Augest 2009)<br /><br /> I need a script that will read the above table and
foreach such row it will<br /> insert two rows as below.<br /><br /> ( 2 , 91 , Augest )<br /> ( 2 , 86 , 2009 )<br
/><br/> 16, 91 and 86 are static values. *value and field together* creates the<br /> primary key.<br
/></blockquote><br/></div> CREATE TEMP TABLE staticfields (f integer);<br /> INSERT INTO staticfields VALUES (91);<br
/>INSERT INTO staticfields VALUES (86);<br /><br /> INSERT INTO metadata (value, field, mydate)<br /> SELECT value, f,
mydate<br/> FROM metadata, staticfields;<br /><font color="#888888"><br /> -- <br />  Richard Huxton<br />  Archonet
Ltd<br/></font></blockquote></div><br /> 

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

Worked perfectly.<br />Thanks Richard.<br /><br /><div class="gmail_quote">On Tue, Jul 1, 2008 at 10:17 AM, Richard
Huxton<<a href="mailto:dev@archonet.com">dev@archonet.com</a>> wrote:<br /><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="Ih2E3d">DhanushkaSamarakoon wrote:<br /><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204,
204,204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Thanks for the reply.<br /> But one problem I have is I need
toloop through all the rows in the table<br /> and in each iteration I need to fetch the value of mydate in to a
variable<br/> and split it to month and year and add two rows with *value, 91, month* (2 ,<br /> 91, Augest) and
*value,86, year* (2 , 86 , 2009)<br /><br /> So I need an idea on<br /> - how to loop through all the rows thats
returnedby a select statement.<br /> - for each statement how to get the value of mydate in to a variable, so<br />
thatI can use SubString to split it in to date and year and use them in the<br /> insert statement.<br
/></blockquote><br/></div> Ah, looking back I see "mydate" isn't actually a date. Note the space in the pattern for
substring()below:<div class="Ih2E3d"><br /><br /> INSERT INTO metadata (value, field, mydate)<br /></div> SELECT
value,91,substring(mydate, '(.+) ')<br /> FROM metadata<br /> UNION ALL<br /> SELECT value, 86, substring(mydate, '
(.+)')<br/> FROM metadata;<br /><br /> Does that do it for you? Try the SELECT clauses by themselves to check if
they'redoing the right thing.<br /><font color="#888888"><br /> -- <br />  Richard Huxton<br />  Archonet Ltd<br
/></font></blockquote></div><br/>