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

reporting and transposition

From: Didier Gasser-Morlay <didiergm(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: reporting and transposition
Date: 2010-02-18 14:53:20
Message-ID: 608b66ce1002180653s12d1a3b7q463b87729f80091e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello,

I have got several reports to write which involve some sort of
transposition. Let me try to explain I have a table which is defined as

Table : results
  id integer not null --- that's the pk
  result_family integer --- that's an fk to a table in which I find a
description  defines the columns on the report
  result_type    integer  --- that's an fk to a table in which I find a
description  defines the rows
  result_value real
Say I have the following descriptions Fam1, Fam2 .... fam10 and type1, type2
... type20


at the end the final report must look like

           FAM1      FAM2      FAM5
Typ1      value                    value
type3                  value
type6     value     value

in other words, not every value exists. that's OK but my real gotcha is that
I do not know when I start how many families exists nor how many types. On
top of that the user can add families and types any time.  On top of that I
need to create adhoc reports on the fly (they represent water sample
analysis to be performed and sample location; not every analysis will have
the same sampling locations not the same analysis to perform)

I thought about creating an ad hoc temp table via execute for each report
and then populate it in pl/sql via execute and return a setof records from
that pl/sql function, but I am concerned about speed and load on the server
and also the effect of creating several hundred temp tables  (when
generating these reports, I'll have to generate between 400 and 500
reports).

Another possibility would be to create one temp table with the max number of
families as columns and populate it using execute o select the proper
column.

These two idea seem a bit heavy to me, is there any other clever way of
going about that ?

thanks in advance for your input

Didier

How would you go about that ?

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-02-18 15:25:31
Subject: Re: reporting and transposition
Previous:From: dipti shahDate: 2010-02-18 11:09:06
Subject: Re: Define permissions at database level

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