Re: merge result sets

On Wed, Jun 21, 2006 at 02:08:29PM +0200, simon wrote:
> > The aggregate stuff should work. something like:
> >
> > SET kategorie = (SELECT comma_aggregate(kategorie_bezeichnung) FROM ...)
> >
> > should do the trick.
> i just found
>
> CREATE FUNCTION comma_aggregate(text,text) RETURNS text AS ' SELECT CASE
> WHEN $1 <> '''' THEN $1 || '', '' || $2 ELSE $2 END; ' LANGUAGE sql
> IMMUTABLE STRICT; CREATE AGGREGATE comma (basetype=text,
> sfunc=comma_aggregate, stype=text, initcond='' );
>
> and this didn't work with the above mentioned querry.
>
> i actually never found any docu about how tor write custom function
> which takes a whole result set no matter how many rows.

Then you havn't looked very hard:

http://www.postgresql.org/docs/current/interactive/sql-createaggregate.html

You created an aggregate called "comma" so that's how you should call
it. This is really no different from the SQL standard min(), max() and
sum() functions.

SET kategorie = (SELECT comma(kategorie_bezeichnung) FROM ...)

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.