Re: How to do this ? - Mailing list pgsql-general

On Wed, May 18, 2011 at 1:25 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

Dear all,

I explain in the simple terms :

Our application stores  data in  a format  that  is  not best  fitted  to analyze.

Table news

category_id   Record_id       field_name             field_value                                                                                                                

78                   21                     Village                  adasrpur               
78                   21                      SOI                      media                     
78                   21                     Heading               CM dies
78                   21                    Description            In the agdadjkagdasgdjkhasdkajhgdhjsajhdgasdhgaksgda .....

80                     22                 SOI                           media
80                    22                     Units                        in the armed forces
80                    22                    EventLoc                   kashmir 
80                    22                    GR                             encounter
80                    22                    Other Perspective      ""
80                     22                     Heading                     A bomb takes 100 lives

78                   23                     Village                  chattarpur              
78                   23                      SOI                      media                     
78                   23                     Heading                PM address nation
78                   23                    Description           on the eve of Republic day Pm addresses nation and ensures safety asjhdgakhgdjla....

80                     22                 SOI                             media
80                    22                     Units                        military academy
80                    22                    EventLoc                   Hyderabad   
80                    22                    GR                             firing
80                    22                    Other Perspective      ""
80                     22                     Heading                     militantas have a firing near military academy

category_table :

category_id                     category_name
78                                    Political
80                                       Criminal
..........
.........
....


Problem :-

1. There are more than 40000 rows and different category_id have different number of rows ( field_name,fild_values)
2. There may be case when different category_id's have different field_name.
3. In future there may be 1000 of categories and millions of news.

Requirement :

 We want the desired data in horizontal format and field_name becomes the table columns for e.g ;

A user inputs category = Criminal then output will be :

category_id         category_name              SOI             Units                         EventLoc         GR               Other Perspective           Heading
80                       Criminal                        media      in the armed forces        Kashmir          encounter         ""                                    A bomb takes 100 lives
80                       Criminal                        media     military academy        Hyderabad          firing                 ""                                  militantas have a firing near military academy

||rly catgory_id 78 has also its rows.

Note that on other category_id column names may change.

My procedure :
1. create a temporary table depending upon field_name column of the corresponding id.
2. Load data in it from user_news table.
3. Select data from the temporary table.

I am not able to load data.
Please guide what is the correct way to achieve this.

I attach my procedure. Please let me know if any other information is required.

I can do it myself if someone show me the path to do this


Thanks & best Regards
Adarsh Sharma

create function user_news_new(text) returns void as $$

declare

name text;
cat_name alias for $1;

begin

CREATE TEMPORARY TABLE temptest(category_id INTEGER,category_name text);



for name in select label_name from category_labels where category_id = (select category_id from category where category_name=cat_name) loop

execute 'alter table temptest add column ' || name || ' text';

end loop;

select * from user_news where category_id=

end;

$$ language plpgsql;


/* category_label table contains all the field_name entries of category_id's */


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This looks like good candidate for "pivot" or "crosstab" functions. Had done something similar under Oracle, but in postgresql, there is tablefunc in contrib. Hope that helps.

SELECT *
FROM crosstab( 'select rowid, attribute, value  from ct  where attribute = ''att2'' or attribute = ''att3''  order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------test1    | val2       | val3       |test2    | val6       | val7       |

Output is generated but it is static, fore.g we have to mention all the output colummn names in the beginning but my column names also be different w.r.t different category_id's

ct(row_name text, category_1 text, category_2 text, category_3 text);

We have to give column names as shown above but it depends upon different category_id', they may be different.


Thanks
(2 rows)