Received: from chatsubo.sprawl.de (root@port-60.ca.kamp.de [195.62.112.252]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA43273 for ; Sat, 15 Apr 2000 15:29:45 -0400 (EDT) (envelope-from finn@chatsubo.sprawl.de) Received: from chatsubo.sprawl.de (finn@localhost [127.0.0.1]) by chatsubo.sprawl.de (8.9.3/8.9.3) with ESMTP id VAA02025 for ; Sat, 15 Apr 2000 21:28:54 +0200 Message-Id: <200004151928.VAA02025@chatsubo.sprawl.de> X-Mailer: exmh version 2.0.2 2/24/98 To: "pgsql-sql@postgresql.org" Subject: going gaga on creating an aggregate... From: Christian Fritze Reply-To: Christian Fritze MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Date: Sat, 15 Apr 2000 21:28:54 +0200 X-Archive-Number: 200004/158 Hi! This one's really driving me mad. Maybe one of you can shed some light on it. If I'm just too dumb to see, then beat me, forgive me and tell me how it's done anyway. Thanks in advance! I'd like to concatenate all values of a text attribute returned by some select statement. So first I define a function to concatenate two text parameters (and no, I don't just use textcat because I want to be sure about the extra blank...) foodb=> create function test_func_1(text, text) returns text as 'select ($1 || \' \') || $2' language 'sql'; CREATE foodb=> select test_func_1('foo', 'bar'); test_func_1 ----------- foo bar (1 row) Ah, seems to work fine... Now for the aggregation: foodb=> create aggregate test_agg_1 ( basetype = text, stype1 = text, sfunc1 = test_func_1, initcond1 = ''); CREATE foodb=> select test_agg_1(textvar) from testtable; ERROR: ExecAgg: no valid transition functions?? What's that beast talking here??? If I leave the initcond1 out of the aggregate's definition, I get the following: foodb=> drop aggregate test_agg_1 text; DROP foodb=> create aggregate test_agg_1 ( basetype = text, stype1 = text, sfunc1 = test_func_1); CREATE foodb=> select test_agg_1(textvar) from testtable; test_agg_1 ---------- (1 row) which is just as silly, because foodb=> select textvar from testtable; textvar ------- foo bar com (3 rows) The things mentioned above happen under postgres 6.4.2. On a 6.5.3 I just got: foodb=> select test_agg_1(textvar) from testtable; ERROR: SQL-language function not supported in this context. Huh??? What context is meant here? And why no SQL functions here? Is my wish so exotic or am I just missing the point? Greetings Christian -- "The sky above the port was the color of television, tuned to a dead channel." -- W.G. --