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
