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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEmSDSIB7bNG8LQkwRAgtxAJ91VEMLSlHz83D5FzZpiX Dm961U6gCdEGEi
SI0wGBPNMV3z7cPSV2DxEDE=
=FO34
-----END PGP SIGNATURE-----

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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/curre...aggregate.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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEmTiwIB7bNG8LQkwRAk8JAJ41fr/d9P5nWHhinqGs2nCYSsR0EwCdGjrO
MNLWRPbXuqfUnhowUdQrHFI=
=EMFG
-----END PGP SIGNATURE-----

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/curre...aggregate.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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match