Re: 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 <dev(at)archonet(dot)com> 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
>