Re: [SQL] Need to Iterate the record in plpgsql
If you do not need information about column types you can use hstore for this purpose. [local]:5432 grayhemp@grayhemp=# select * from r limit 1; a | b | c ---+---+--- 1 | 2 | 3 (1 row) [local]:5432 grayhemp@grayhemp=# select * from each((select hstore(r) from r limit 1)); key | value -+--- a | 1 b | 2 c | 3 (3 rows) The key and value columns here of the text type. On Fri, Aug 31, 2012 at 6:55 PM, Yelai, Ramkumar IN BLR STS wrote: > Hi All, > > I am facing a issue in Iterating the RECORD. > > The problem is, I would like to iterate the RECORD without using sql query, > but as per the syntax I have to use query as shown below. > > FOR target IN query LOOP > statements > END LOOP [ label ]; > > In my procedure, I have stored one of the procedure output as record, which > I am later using in another iteration. Below is the example > > > CREATE OR REPLACE FUNCTION test2() > > Rec1 RECORD; > Rec2 RECORD; > Rec3 RECORD; > > SELECT * INTO REC1 FROM test(); > > FOR REC2 IN ( select * from test3()) > LOOP > FOR REC3 IN REC2 --- this syntax does not allowed by Postgresql > LOOP > > END LOOP > END LOOP > > As per the example, How can I iterate pre stored record. > > Please let me know if you have any suggestions. > > Thanks & Regards, > Ramkumar > > > > > -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question regarding modelling for time series
Hi there, I want to use a database for storing economic time series. An economic time series can be thought of as something like this: NAME| RELEASE_DATE | REPORTING_DATE | VALUE +--++--- Unemployment US | 2011/01/01 | 2010/12/01 | xxx Unemployment US | 2011/02/01 | 2011/01/01 | xxx Unemployment US | 2011/03/01 | 2011/02/01 | xxx The release date is the date on which the data provider published the value and the reporting date is the date to which the value refers (read: In Dec, 2010 the unemployment was X but this has not been known until 2011/01/01). However, that's not the whole story. On each "release date" not only ONE value is released but in some cases the values for previous reporting_dates are changed. So, the table could read like this: NAME| RELEASE_DATE | REPORTING_DATE | VALUE +--++--- Unemployment US | 2011/01/01 | 2010/12/01 | xxx Unemployment US | 2011/01/01 | 2010/11/01 | xxx Unemployment US | 2011/01/01 | 2010/10/01 | xxx Unemployment US | 2011/02/01 | 2010/10/01 | xxx Unemployment US | 2011/02/01 | 2010/11/01 | xxx Unemployment US | 2011/02/01 | 2010/12/01 | xxx Unemployment US | 2011/02/01 | 2011/01/01 | xxx [...] So, there are now mainly three questions to be answered: 1) "get me the time series [reporting_date, value] of unemployment as it is now seen", so give all reporting_date,value tuples with the most recent release_date. 2) "get me the time series [reporting_date, value] as it was published/known to the market", so that means, in this case, give this list: Unemployment US | 2011/01/01 | 2010/12/01 | xxx Unemployment US | 2011/02/01 | 2011/01/01 | xxx Unemployment US | 2011/03/01 | 2011/02/01 | xxx 3) the same as (1) but with one enhancement: if the most recent release has a history of N month, but all releases has a history of N+X month, the time series from the most recent release should be delivered plus the older values (in terms of reporting_dates) from the second most recent release plus the more older values from the third most recent release and so on. So, I thought of a relational data base model like that: TABLE 'ts' (TimeSeries) PK:id | name TABLE 'rs' (ReleaseStages) PK:id | FK:ts_id | release_date TABLE 'r' (Releases) PK:id | FK:rs_id | reporting_date | value Is this an appropriate model? If yes, how could I answer the three questions above in terms of SQL/Stored Procedures? If no, what would you suggest? And: If the datasets grow further, will be an RDBMS the right model for time series storage? Any ideas on what else I could use? Thank you very much! --Alex -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question regarding modelling for time series
On Wed, Sep 5, 2012 at 12:16 AM, Alex Grund wrote: > So, I thought of a relational data base model like that: It is worth to make like this TABLE 'ts' (TimeSeries) PK:id | name TABLE 'r' (Releases) PK:id | FK:ts_id | release_date | reporting_date | value It is a little more redundant but easier to work with data. > 1) "get me the time series [reporting_date, value] of unemployment as > it is now seen", so give all reporting_date,value tuples with the most > recent release_date. SELECT * FROM r WHERE ts_id = 123 AND release_date = (SELECT max(release_date) FROM r) ORDER BY reporting_date; > 2) "get me the time series [reporting_date, value] as it was > published/known to the market", so that means, in this case, give this > list: > Unemployment US | 2011/01/01 | 2010/12/01 | xxx > Unemployment US | 2011/02/01 | 2011/01/01 | xxx > Unemployment US | 2011/03/01 | 2011/02/01 | xxx If I understand it correct it will look like SELECT DISTINCT ON (release_date) * FROM r WHERE ts_id = 123 ORDER BY release_date, reporting_date DESC; > 3) the same as (1) but with one enhancement: if the most recent > release has a history of N month, but all releases has a history of > N+X month, the time series from the most recent release should be > delivered plus the older values (in terms of reporting_dates) from the > second most recent release plus the more older values from the third > most recent release and so on. I am not quite understand what is meant here. Could you please provide more explanation and some examples. > And: If the datasets grow further, will be an RDBMS the right model > for time series storage? Any ideas on what else I could use? It will be. You will probably need some more optimization/partitioning depending on the data distribution however it can be done later. > > > > Thank you very much! > > > --Alex > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
