Re: Need a sample Postgre SQL script

------=_Part_21549_8134930.1214923982630
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 <dev@[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
>

------=_Part_21549_8134930.1214923982630
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 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 <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
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.<br>
<br><div class="gmail_quote">On Tue, Jul 1, 2008 at 9:42 AM, Richard
Huxton &lt;<a href="mailto:dev@[EMAIL PROTECTED]
">dev@[EMAIL PROTECTED]
>&gt;
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">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&#39;m kind of new to Postgre and I need some advice.<br>
</blockquote>
<br></div>
No problem. It&#39;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 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 , Augest 2009)<br>
<br>
I need a script that will read the above table and for each 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>
 &nbsp;Richard Huxton<br>
 &nbsp;Archonet Ltd<br>
</font></blockquote></div><br>

------=_Part_21549_8134930.1214923982630--