Please reply to the list, not directly to me. My reply follows. On 20/05/11 14:47, Adarsh Sharma wrote: > But Sometimes when I got stucked , I have no other option. Like I > stucked from the past 4 days to solve the attached problem. Sure. Sometimes you get stuck, and that's what mailing lists are great for. All I was trying to say is that the more effort you can show people you've put in _before_ asking for help and the more effort you put into writing a clear and complete question when you do ask for help, the more effort they'll usually put into helping you. > The problem is Can we create and populate a dynamic table A from another > table B where > table A column names are values of one column of table B & > Table A column values are the values of 2nd column of table B. > > I will do it if I got the architecture to achieve this. You're trying to transform an EAV (Entity-Attribute-Value) schema, otherwise known as a key/value schema, into a real relational schema. http://en.wikipedia.org/wiki/Entity-attribute-value_model Like many relational databases, PostgreSQL isn't very well suited to that, because it expects relations (tables) to have a fairly fixed set of columns. It doesn't deal well with views that potentially have a different set of columns each time they're executed. However, PostgreSQL has a couple of ways around that: the 'RECORD' data type, and the 'hstore' data type. I have the feeling that what you want might - kind of - be possible by combining a crosstab query with the hstore record constructor in PostgreSQL 9.0 and above. That'll let you produce a view like: category_id Record_id fields 78 21 {Village:adasrpur, SOI:media, Heading:CM dies} etc. Come to think of it, it'd be possible to do in 8.4 and earlier in PL/PgSQL, but I'd have to have a play with that. Anyway, check out: http://www.postgresql.org/docs/current/static/tablefunc.html http://www.postgresql.org/docs/current/static/hstore.html in particular the crosstab(text,text) form and the hstore(record) constructor. Have a play. It'd be helpful to have your sample data in a useful format like a list of INSERT statements, a pg_dump, or the output of \COPY rather than just a plain text list. It'd make testing things easier. I'd like to have a play, but I can't be bothered making up a sample data set or converting yours by hand. BTW, Personally I think you're usually better off using hstore in the frist place ratherthan EAV, but you're probably already committed to an EAV model. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
