Re: Need a sample Postgre SQL script

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

Worked perfectly.
Thanks Richard.

On Tue, Jul 1, 2008 at 10:17 AM, Richard Huxton <dev@[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
>

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

Worked perfectly.<br>Thanks Richard.<br><br><div class="gmail_quote">On
Tue, Jul 1, 2008 at 10:17 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;">
Thanks for the reply.<br>
But one problem I have is I need to loop 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 returned by a select
statement.<br>
- for each statement how to get the value of mydate in to a variable,
so<br>
that I 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 &quot;mydate&quot; isn&#39;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, &#39;(.+) &#39;)<br>
FROM metadata<br>
UNION ALL<br>
SELECT value, 86, substring(mydate, &#39; (.+)&#39;)<br>
FROM metadata;<br>
<br>
Does that do it for you? Try the SELECT clauses by themselves to check if
they&#39;re doing the right thing.<br><font color="#888888">
<br>
-- <br>
 &nbsp;Richard Huxton<br>
 &nbsp;Archonet Ltd<br>
</font></blockquote></div><br>

------=_Part_8866_25249213.1215018762981--