Thread: merge result sets

merge result sets

From

simon

Date:

20 June 2006, 10:32:44

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:

20 June 2006, 20:31:12

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:

20 June 2006, 22:10:07

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:

21 June 2006, 10:24:03

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:

21 June 2006, 10:35:14

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

signature.asc

Re: merge result sets

From

simon

Date:

21 June 2006, 12:09:04

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:

21 June 2006, 12:17:10

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

signature.asc

Re: merge result sets

From

simon

Date:

21 June 2006, 13:13:26

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