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 E9E6D3A5D6F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 13 Jan 2005 11:31:24 +0000 (GMT)
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 26935-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 13 Jan 2005 11:31:18 +0000 (GMT)
Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.195])
	by svr1.postgresql.org (Postfix) with ESMTP id 1540E3A5D5E
	for <pgsql-sql@postgresql.org>; Thu, 13 Jan 2005 11:31:20 +0000 (GMT)
Received: by rproxy.gmail.com with SMTP id i8so300086rne
	for <pgsql-sql@postgresql.org>; Thu, 13 Jan 2005 03:31:20 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
	b=GdWOISlmAxMAhUdLLBqDS1STM9e3nOw7/z7y6CdxOpq5gZEP0YfWBu/9byuxrOhovDEDH76GzpsuiA71gt7IFpKTBEd45sNmXpWLMlzZUSBS74sVDQiU2LnSOEq6We0o5V0eCkwi1geCz1qH3ouuxkGfC+m3d5cbFO51qNEYO/g=
Received: by 10.39.3.60 with SMTP id f60mr205044rni;
	Thu, 13 Jan 2005 03:31:20 -0800 (PST)
Received: by 10.38.163.47 with HTTP; Thu, 13 Jan 2005 03:31:20 -0800 (PST)
Message-ID: <758d5e7f0501130331546d9f6a@mail.gmail.com>
Date: Thu, 13 Jan 2005 12:31:20 +0100
From: Dawid Kuroczko <qnex42@gmail.com>
Reply-To: Dawid Kuroczko <qnex42@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Columns into rows.
In-Reply-To: <758d5e7f0501130245e2e6fb1@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
References: <758d5e7f05011302265be7e12a@mail.gmail.com>
	<758d5e7f0501130245e2e6fb1@mail.gmail.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.54 tagged_above=0 required=5
	tests=FROM_ENDS_IN_NUMS, RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200501/79
X-Sequence-Number: 19867

On Thu, 13 Jan 2005 11:45:52 +0100, Dawid Kuroczko <qnex42@gmail.com> wrote:
> > SELECT * FROM (SELECT id, path AS path_a FROM new_table_paths WHERE
> > pathtype = 'a') AS a NATURAL FULL OUTER JOIN (SELECT id, path AS
> > path_bb FROM new_table_paths WHERE pathtype = 'bb') AS bb WHERE id =
> > <<NUM>>;

Part of the problem solved:

CREATE AGGREGATE array_aggregate (basetype = anyelement, sfunc =
array_append, stype = anyarray, initcond = '{}');
SELECT id,array_aggregate(pathtype||'='||path) from new_table_paths group by id;

I.e. have a nice array of type=value pairs.

I have however failed to write a function which would take text[] and
return columns.  I tried using RETURN record type, but so far it does
not return
anything useful (just a parenthesis enclosed list of column values).

Any hints?