Thread: merge result sets

merge result sets

From
simon
Date:
hi all

i'm using postgres 7.3

my problem is i want to build a helper table:

UPDATE studienmodul_summary
               SET kategorie = (SELECT kategorie_bezeichnung

if the SELECT gives back just one result row, everthing is easy. my
problem is, there is sometimes more than one result row.

is there a way to concatenate all result rows and insert them in one
field?

any hint is very much appreciated.

thanks
simon

--
Simon Litwan                               simon.litwan@wyona.com
Wyona Inc.  -   Open Source Content Management   -   Apache Lenya
http://www.wyona.com                      http://lenya.apache.org


Re: merge result sets

From
Bruno Wolff III
Date:
On Tue, Jun 20, 2006 at 12:06:24 +0200,
  simon <simon.litwan@wyona.com> wrote:
> hi all
>
> i'm using postgres 7.3
>
> my problem is i want to build a helper table:
>
> UPDATE studienmodul_summary
>                SET kategorie = (SELECT kategorie_bezeichnung
>
> if the SELECT gives back just one result row, everthing is easy. my
> problem is, there is sometimes more than one result row.
>
> is there a way to concatenate all result rows and insert them in one
> field?

Yes. You can have the subselect call a custom aggregate function that does
this for you. Sample code for doing this has been posted on the list
multiple times and you should be able to find it in the archives.

Re: merge result sets

From
simon
Date:
On Die, 2006-06-20 at 15:34 -0500, Bruno Wolff III wrote:
> On Tue, Jun 20, 2006 at 12:06:24 +0200,
>   simon <simon.litwan@wyona.com> wrote:
> > hi all
> >
> > i'm using postgres 7.3
> >
> > my problem is i want to build a helper table:
> >
> > UPDATE studienmodul_summary
> >                SET kategorie = (SELECT kategorie_bezeichnung
> >
> > if the SELECT gives back just one result row, everthing is easy. my
> > problem is, there is sometimes more than one result row.
> >
> > is there a way to concatenate all result rows and insert them in one
> > field?
>
> Yes. You can have the subselect call a custom aggregate function that does
> this for you. Sample code for doing this has been posted on the list
> multiple times and you should be able to find it in the archives.

thanks for this hint i didn't konw about the custom aggregate function.
i found comma_aggregate(text,text) amd similar examples.
unfortunatly i didn't found something like comma_aggregate(SELECT...).

is it possible to write an aggregate function that takes the result rows
of any number and makes a long string out of it?

it would be great if someone would have done something before and is
willing to share.
but hints where to find docu and/or howtos about writting
customaggregate functions are also very welcom.

simon
>
--
Simon Litwan                               simon.litwan@wyona.com
Wyona Inc.  -   Open Source Content Management   -   Apache Lenya
http://www.wyona.com                      http://lenya.apache.org


Re: merge result sets

From
simon
Date:
On Mit, 2006-06-21 at 00:09 +0200, simon wrote:
> On Die, 2006-06-20 at 15:34 -0500, Bruno Wolff III wrote:
> > On Tue, Jun 20, 2006 at 12:06:24 +0200,
> >   simon <simon.litwan@wyona.com> wrote:
> > > hi all
> > >
> > > i'm using postgres 7.3
> > >
> > > my problem is i want to build a helper table:
> > >
> > > UPDATE studienmodul_summary
> > >                SET kategorie = (SELECT kategorie_bezeichnung
> > >
> > > if the SELECT gives back just one result row, everthing is easy. my
> > > problem is, there is sometimes more than one result row.
> > >
> > > is there a way to concatenate all result rows and insert them in one
> > > field?
> >
> > Yes. You can have the subselect call a custom aggregate function that does
> > this for you. Sample code for doing this has been posted on the list
> > multiple times and you should be able to find it in the archives.
>
> thanks for this hint i didn't konw about the custom aggregate function.
> i found comma_aggregate(text,text) amd similar examples.
> unfortunatly i didn't found something like comma_aggregate(SELECT...).
>
> is it possible to write an aggregate function that takes the result rows
> of any number and makes a long string out of it?
>
> it would be great if someone would have done something before and is
> willing to share.
> but hints where to find docu and/or howtos about writting
> customaggregate functions are also very welcom.
>
> simon
> >
or in other words, i just would like to know how to rewrite

SET kategorie = array_to_string ((SELECT ARRAY (SELECT
kategorie_bezeichnung

so it works in psql7.3 as well.

simon


--
Simon Litwan                               simon.litwan@wyona.com
Wyona Inc.  -   Open Source Content Management   -   Apache Lenya
http://www.wyona.com                      http://lenya.apache.org


Re: merge result sets

From
Martijn van Oosterhout
Date:
On Wed, Jun 21, 2006 at 12:23:44PM +0200, simon wrote:
> or in other words, i just would like to know how to rewrite
>
> SET kategorie = array_to_string ((SELECT ARRAY (SELECT
> kategorie_bezeichnung
>
> so it works in psql7.3 as well.

The aggregate stuff should work. something like:

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

should do the trick.

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

Attachment

Re: merge result sets

From
simon
Date:
On Mit, 2006-06-21 at 12:34 +0200, Martijn van Oosterhout wrote:
> On Wed, Jun 21, 2006 at 12:23:44PM +0200, simon wrote:
> > or in other words, i just would like to know how to rewrite
> >
> > SET kategorie = array_to_string ((SELECT ARRAY (SELECT
> > kategorie_bezeichnung
> >
> > so it works in psql7.3 as well.
>
> 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.

thanks anyway
simon



>
> Have a nice day,
--
Simon Litwan                               simon.litwan@wyona.com
Wyona Inc.  -   Open Source Content Management   -   Apache Lenya
http://www.wyona.com                      http://lenya.apache.org


Re: merge result sets

From
Martijn van Oosterhout
Date:
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@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: merge result sets

From
simon
Date:
On Mit, 2006-06-21 at 14:16 +0200, Martijn van Oosterhout wrote:
> 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,

thanks very much. you're right i should have read the docu harder. but
now everthing works fine.
you made my day.

simon

--
Simon Litwan                               simon.litwan@wyona.com
Wyona Inc.  -   Open Source Content Management   -   Apache Lenya
http://www.wyona.com                      http://lenya.apache.org