Re: dynamic crosstab

On Tue, Feb 19, 2008 at 11:56:08AM -0300, Alvaro Herrera wrote:
> Joe Conway wrote:
> > Erik Jones wrote:
> >> See how postgres handles filling the NULLs for you? What you'd
> >> really want to do with this would be to define some functions
> >> for setting and getting a person's answers to a given question
> >> or set of questions so that you could implement some kind of
> >> data integrity with regards to question ids and indices into the
> >> answers arrays such as in the example above you'd want to prevent
> >> an entry at index 7 when there is no entry in the questions
> >> table for question_id=7.
> >
> > It occurs to me that it shouldn't be terribly difficult to make an
> > alternate version of crosstab() that returns an array rather than
> > tuples (back when crosstab() was first written, Postgres didn't
> > support NULL array elements). Is this worth considering for 8.4?
>
> How about returning generic rows? Is that possible?

One hack I've used in the past to get those is serializing the rows:
XML, YAML and most recently JSON.

> It would be really neat if you didn't have to specify the return
> type in the query that invoked the crosstab.

It would be handy :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate