Thread: pivot-like transformation
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
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
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