From: Torsten Lange <t.lange@ufz.de>
To: Joe Conway <mail@joeconway.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: pivot-like transformation
Date: Mon, 21 Jun 2004 10:08:46 +0200
Message-ID: <40D6978E.7040501@ufz.de> (raw)
In-Reply-To: <40D60C8B.3060609@joeconway.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: t.lange@ufz.de, mail@joeconway.com
Subject: Re: pivot-like transformation
In-Reply-To: <40D6978E.7040501@ufz.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox