Skip site navigation (1) Skip section navigation (2)

Re: custom crosstab question

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com>
Cc: Joe Conway <mail(at)joeconway(dot)com>, PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: custom crosstab question
Date: 2007-04-25 20:01:52
Message-ID: 462FB3B0.1040206@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
Michael Swierczek wrote:
> On 4/25/07, Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com> wrote:
>> Joe,
>>      That's exactly what I needed.   I completely missed that it would
>> be possible with the tablefunc/crosstab.   Since you're the main
>> (only?) name I see associated with that code, I'm sure you would know.
>>
>> -Mike
>>
>
> I spoke too soon, there's an additional factor at play that
> complicates things.
> Most of the questions take a single answer, but the multiple select
> questions can have several answers.   So for a regular question with
> code 'drug', a given survey_event can have 0 or 1 entries in the
> answer table with question_code 'drug'.  For a multiple select
> question like, 'health', 0-7 entries are possible in the answer table,
> and we want them exported as columns 'health0', 'health1', 'health2'
> through 'health7' and each column populated according to whether that
> section of the multiple select was chosen.
>
> However, I fear I am crossing from "I have a novice question" into
> "This is complicated enough that requesting help is exploiting the
> generosity of the community for free consulting services."  I won't
> pursue it any further here.
Sometimes, in cases like this, where there is business logic 
intermingled with database logic, the best option is to move your 
reporting code to the client.  While this may result in a performance 
hit, more than a single query, and some data structures slightly more 
complex than rows in a table, the flexibility of this route might be 
worth the effort. 

Of course, you are using postgresql, so you can certainly write 
functions within the database that return data structures more complex 
than single rows from within the database.  For example, you could write 
a function using pl/perl or pl/python (or java, etc.) that returns an 
XML chunk that represents the report results and then use XSLT on the 
client side to format that result into whatever you like.  The 
possibilities are pretty endless. 

Sean

In response to

pgsql-novice by date

Next:From: Fei LiuDate: 2007-04-25 21:37:09
Subject: how to view the content of schema pg_catalog?
Previous:From: Michael SwierczekDate: 2007-04-25 17:45:24
Subject: Re: custom crosstab question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group