On 20/05/11 17:33, Adarsh Sharma wrote:
> Craig Ringer wrote:
>>
>> category_id Record_id fields
>> 78 21 {Village:adasrpur, SOI:media, Heading:CM dies
>>
>
> Yes , but slight modification , I want Village, SOI Heading as column
> names and adasrpur,media and CM dies their values as there may 1000 of
> rows of it.
You're out of luck, then. You want a view with dynamic columns, and
PostgreSQL does not support that.
You can have have a *function* with a dynamic record as output, but then
you have to name the columns in the query. That's how crosstab works.
You can also have a result where all your dynamic data - the stuff where
you don't know the keys in advance - is a single column of a data type
like 'hstore' that contains key/value mappings. From the above, you
don't seem to want that.
I suspect that in your case, your best bet would be a two-query
solution. Figure out your WHERE clause, then run a query to find out
what keys match he WHERE clause. Use that in your application to produce
a crosstab query with an appropriate column list.
If you really need to do this as a view, I think you're stuffed. You'll
never get dynamic column lists in a view.
> I read hstore first time & find it difficult to understand because I
> don't want the output in one column :
>
> CREATE TABLE test (col1 integer, col2 text, col3 text);
> INSERT INTO test VALUES (123, 'foo', 'bar');
>
> SELECT hstore(t) FROM test AS t;
> hstore
> ---------------------------------------------
> "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
> (1 row)
Why not? Is the problem converting a hstore value into a set of values
client-side? Something else?
Maybe it would help if you took a step back and explained why you need
this exacty format - a view with dynamic columns. It's a weird
requirement, and it makes me wonder if you're really looking for
something else and have settled on a dynamic-column view as the "only"
solution when there migh tbe an easier way.
What constraints are you under? What app is involved? What language(s)?
Is it old/unmaintained code, or something new and under development?
What role do you play in it?
--
Craig Ringer
