From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Dhanushka Samarakoon <dhanu80(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Need a sample Postgre SQL script |
Date: | 2008-07-01 15:17:30 |
Message-ID: | 486A4A8A.6070606@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fernando Hevia | 2008-07-01 20:12:30 | column default dependant on another columns value |
Previous Message | Dhanushka Samarakoon | 2008-07-01 14:53:02 | Re: Need a sample Postgre SQL script |