pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpivot-like transformation
3+ messages / 2 participants
[nested] [flat]
* pivot-like transformation
@ 2004-06-20 19:44 Torsten Lange <t.lange@ufz.de>
2004-06-20 22:15 ` Re: pivot-like transformation Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 3+ messages in thread
From: Torsten Lange @ 2004-06-20 19:44 UTC (permalink / raw)
To: pgsql-sql
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
My first attempts to only give back the original table within a function
failed. There are tons of examples to select one value into a variable...
But for more I browsed through the net and my book but I couldn't find
anything that works. I'm new to PL/PgSQL. Could anyone show me a _very_simple_
example of how to to read (the columns I like) from a table and return the
columns (I like) using tablename%rowtype and an other possiblity if
exists.
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?
Thank
you,
Torsten
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pivot-like transformation
2004-06-20 19:44 pivot-like transformation Torsten Lange <t.lange@ufz.de>
@ 2004-06-20 22:15 ` Joe Conway <mail@joeconway.com>
2004-06-21 08:08 ` Re: pivot-like transformation Torsten Lange <t.lange@ufz.de>
0 siblings, 1 reply; 3+ messages in thread
From: Joe Conway @ 2004-06-20 22:15 UTC (permalink / raw)
To: Torsten Lange <t.lange@ufz.de>; +Cc: pgsql-sql
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 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
HTH,
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pivot-like transformation
2004-06-20 19:44 pivot-like transformation Torsten Lange <t.lange@ufz.de>
2004-06-20 22:15 ` Re: pivot-like transformation Joe Conway <mail@joeconway.com>
@ 2004-06-21 08:08 ` Torsten Lange <t.lange@ufz.de>
0 siblings, 0 replies; 3+ messages in thread
From: Torsten Lange @ 2004-06-21 08:08 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-06-21 08:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-20 19:44 pivot-like transformation Torsten Lange <t.lange@ufz.de>
2004-06-20 22:15 ` Joe Conway <mail@joeconway.com>
2004-06-21 08:08 ` Torsten Lange <t.lange@ufz.de>
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