X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6C944D1B173 for ; Sun, 20 Jun 2004 19:15:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68726-08 for ; Sun, 20 Jun 2004 22:15:40 +0000 (GMT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 555A7D1B1BC for ; Sun, 20 Jun 2004 19:15:36 -0300 (ADT) Received: from [192.168.4.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 1836944; Sun, 20 Jun 2004 15:07:56 -0700 Message-ID: <40D60C8B.3060609@joeconway.com> Date: Sun, 20 Jun 2004 15:15:39 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040510 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Torsten Lange Cc: pgsql-sql@postgresql.org Subject: Re: pivot-like transformation References: <1087760651.40d5e90bbd93e@webmail.ufz.de> In-Reply-To: <1087760651.40d5e90bbd93e@webmail.ufz.de> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/172 X-Sequence-Number: 18117 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