Date: Mon, 21 Jun 2004 10:08:46 +0200
From: Torsten Lange <t.lange@ufz.de>
Subject: Re: pivot-like transformation
In-reply-to: <40D60C8B.3060609@joeconway.com>
To: Joe Conway <mail@joeconway.com>
Cc: pgsql-sql@postgresql.org
Message-id: <40D6978E.7040501@ufz.de>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207)
References: <1087760651.40d5e90bbd93e@webmail.ufz.de>
	<40D60C8B.3060609@joeconway.com>

Joe Conway schrieb:

> Torsten Lange wrote:
>
>> Hello,       I have a table with measurement values and columns like 
>> this:       analyses(id, sample_id, parameter[temperatur...], value, 
>> unit[?C...], error)              With PL/PgSQL at the end I want try 
>> to perform a pivot-like arrangement of these data:       
>> sample_id|Temperature [?C]|parameter2 [mg/L]|...|parameterN 
>> [L/year]       
>> ---------+----------------+-----------------+---+-------------------       
>>     5    |           23.00|            0.036|...|              
>> 35.1       
>
>
> Not a direct answer with respect to plpgsql, but for pivot table 
> functionality see the contrib/tablefunc function crosstab().

I don't like the idea to have a table in another schema in order to 
create a pivot-like arrangement. Anyway, this wasn't really my need. I 
got stuck at the very basics (sorry for that) - reading data from a 
table, doing something with them, like printing to the screen. I tried 
the example at
http://www.postgresql.org/docs/7.4/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES
and got the error message something like "table_name returns more then 
one row...". I cannot recall it accurately since I did it at home and 
now I'm at work.

>> I also found very few sources about handling of arrays and how to 
>> fill them up      with query results... things like this. Does anyone 
>> know a more comprehensive      source?  
>
>
> Have you looked at the online docs?
> http://www.postgresql.org/docs/7.4/static/arrays.html
> http://www.postgresql.org/docs/7.4/static/functions-array.html
> http://www.postgresql.org/docs/7.4/static/functions-comparisons.html
> http://www.postgresql.org/docs/7.4/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

...at first

Best regards, Torsten